Re: Syntax for partitioning

On 29 Oct 2009, at 02:15, Itagaki Takahiro wrote:

> I'd like to improve partitioning feature in 8.5.
> Kedar-san's previous work is wonderful, but I cannot see any updated
> patch.
> http://archives.postgresql.org/message-id/bd8134a40906080702s96c90a9q3bbb581b9bd0d5d7@mail.gmail.com
>
> So, I'll take over the work if there are no ones to do it.
> I'm thinking to add syntax support first. Table partitioning was
> proposed many times, but it is still not applied into core.
> The reason is it is too difficult to make perfect partitioning
> feature at once. I think syntax support is a good start.
>
> First, I will add syntax for CREATE TABLE, ALTER TABLE ADD/DROP
> PARTITION.
> The syntax is borrowed from from Oracle and MySQL. Their
> characteristics
> are using "LESS THAN" in range partitioning. The keyword "PARTITION"
> is
> added to the full-reserved keyword list to support ADD/DROP PARTITION.
>
> Those syntax is merely a syntax sugar for INHERITS with CHECK.
> Declarations
> are translated into CHECK constraints. I have a plan to adjust
> pg_dump to
> dump definitions of partitioning in the correct format, but the actual
> implementation will be still based on constraint exclusion. In
> addition,
> hash partitioning is not implemented; syntax is parsed but "not
> implemented"
> error are raised for now.
>
> Here is syntax I propose:
> ----
> ALTER TABLE table_name ADD PARTITION name ...;
> ALTER TABLE table_name DROP PARTITION [IF EXISTS] name [CASCADE |
> RESTRICT];
>
> Range partitioning:
> CREATE TABLE table_name ( columns )
> PARTITION BY RANGE ( a_expr )
> (
> PARTITION name VALUES LESS THAN [(] const [)],
> PARTITION name VALUES LESS THAN [(] MAXVALUE [)] -- overflow
> partition
> );
>
> List partitioning:
> CREATE TABLE table_name ( columns )
> PARTITION BY LIST ( a_expr )
> (
> PARTITION name VALUES [IN] ( const [, ...] ),
> PARTITION name VALUES [IN] [(] DEFAULT [)] -- overflow
> partition
> );
>
> Hash partitioning:
> CREATE TABLE table_name ( columns )
> PARTITION BY HASH ( a_expr )
> PARTITIONS num_partitions;
>
> CREATE TABLE table_name ( columns )
> PARTITION BY HASH ( a_expr )
> (
> PARTITION name,
> ...
> );
>
> Note:
> * Each partition can have optional WITH (...) and TABLESPACE clauses.
> * '(' and ')' are optional to support both Oracle and MySQL syntax.
> ----
>
> Comments welcome.

+1000

Thanks !

(most anticipated feature for 8.5, here, next to replication [well, I
am interested in multi master, but that's not going to happen :P ] )