Re: Auto creation of Partitions

Simon,

What happens to the data when the function is dropped or replaced?

- Luke

Msg is shrt cuz m on ma treo

-----Original Message-----
From: Simon Riggs [mailto:simon(at)2ndquadrant(dot)com]
Sent: Friday, March 09, 2007 06:20 AM Eastern Standard Time
To: NikhilS
Cc: Shane Ambler; Luke Lonergan; Zeugswetter Andreas ADI SD; Peter Eisentraut; pgsql-hackers(at)postgresql(dot)org
Subject: Re: [HACKERS] Auto creation of Partitions

On Fri, 2007-03-09 at 11:48 +0530, NikhilS wrote:
> Hi,
>
> On 3/9/07, Shane Ambler <pgsql(at)sheeky(dot)biz> wrote:
>
> > Note to Nikhil: Make sure the new syntax doesn't prevent
> partitions from
> > being placed upon multiple tablespaces in some manner, at
> CREATE TABLE
> > time.
>
> What if the syntax was something like -
>
> CREATE TABLE tabname (
> ...
> ...
> ) PARTITION BY
> HASH(expr)
> | RANGE(expr)
> | LIST(expr)
> [PARTITIONS num_partitions] /* will apply to HASH only for
> now*/
> [PARTITION partition_name CHECK(...) [USING TABLESPACE
> tblspcname],
> PARTITION partition_name CHECK(...) [USING TABLESPACE
> tblspcname]
> ...
> ];
>
>
> And (if we use the ALTER TABLE to add partitions)
>
> ALTER TABLE tabname
> ADD PARTITION partition_name CHECK(...)
> [USING TABLESPACE tblspcname];
>
>
>
> We could as well drop the USING part.

Why would we support HASH partitions?
If you did, the full syntax for hash clusters should be supported.

If we do the CHECK clauses like that then we still have don't have a
guaranteed non-overlap between partitions. It would be easier to use
Oracle syntax and then construct the CHECK clauses from that.

Also, the syntax needs to be fairly complex to allow for a mixture of
modes, e.g. range and list partitioning. That is currently possible
today and the syntax for doing that is IMHO much simpler than the Oracle
"simple" way of specifying it.

An alternative is to provide a partitioning function which decides which
partition each values goes into.

PARTITION FUNCTION which_partition(date_col)

The partition function must return an unsigned integer > 0, which would
correspond to particular partitions. Partitions would be numbered 1..N,
and named tablename_partM where 1 <= M <= N.

The input and contents of the partition function would be up to the
user. e.g.

CREATE FUNCTION range_partition(date date_col)
{
if (date_col < D1)
return 1;
else if (date_col < D2)
return 2;
else if (date_col < D3)
return 3;

return 4;
}

Doing it this way would allow us to easily join two tables based upon a
common partition function.

In time, I would suggest we support both ways: declarative and
functional.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com