Re: Syntax for partitioning

2009/10/29 Itagaki Takahiro <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp>:
> 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.

+1

Pavel

>
> Regards,
> ---
> ITAGAKI Takahiro
> NTT Open Source Software Center
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>