Re: Syntax for partitioning
От | Pavel Stehule |
---|---|
Тема | Re: Syntax for partitioning |
Дата | |
Msg-id | 162867790910282139p3cffb105r5a16bc0f02a3cb7a@mail.gmail.com обсуждение исходный текст |
Ответ на | Syntax for partitioning (Itagaki Takahiro <itagaki.takahiro@oss.ntt.co.jp>) |
Список | pgsql-hackers |
2009/10/29 Itagaki Takahiro <itagaki.takahiro@oss.ntt.co.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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >
В списке pgsql-hackers по дате отправления: