Re: Syntax for partitioning - Mailing list pgsql-hackers

Hi,

> 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.

Guess we are back to square one again on Partitioning :), but as long
as someone is willing to walk the whole nine yards with it, that would
be just great!

I had proposed Oracle style syntax a while back and had also submitted
a WIP patch then. Again then my motive was to move forward in a
piece-meal fashion on this feature. First solidify the syntax, keep
using the existing inheritance mechanism and go one step at a time. I
think a feature like Partitioning needs this kind of an approach,
because it might turn out to be a lot of work with a lot of very many
sub items.

So +1 on solidifying the syntax first and then sorting out the other
minute, intricate details later..

Regards,
Nikhils

>
> 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.
>
> Regards,
> ---
> ITAGAKI Takahiro
> NTT Open Source Software Center
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>



--
http://www.enterprisedb.com