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 ] )