Syntax for partitioning
in [PgSql]
|
Prev: [HACKERS] pg_read_file() and non-ascii input file
Next: [HACKERS] Proposal - temporal contrib module
From: Pavel Stehule on 29 Oct 2009 00:39 2009/10/29 Itagaki Takahiro <itagaki.takahiro(a)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(a)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(a)postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Nikhil Sontakke on 29 Oct 2009 06:35 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(a)postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > -- http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Grzegorz Jaskiewicz on 29 Oct 2009 09:56 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(a)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 ] ) -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Heikki Linnakangas on 29 Oct 2009 12:58 Itagaki Takahiro wrote: > The keyword "PARTITION" is > added to the full-reserved keyword list to support ADD/DROP PARTITION. Any chance to avoid that? PARTITION seems like something people might well use as a column or variable name. OTOH, it is reserved in SQL2008 and SQL2003. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Greg Stark on 29 Oct 2009 13:33
On Thu, Oct 29, 2009 at 3:35 AM, Nikhil Sontakke <nikhil.sontakke(a)enterprisedb.com> wrote: > So +1 on solidifying the syntax first and then sorting out the other > minute, intricate details later.. I like that idea as well but I have a concern. What will we do with pg_dump. If the PARTITION commands are just syntactic sugar for creating constraints and inherited tables then pg_dump will have to generate the more generic commands for those objects. When we eventually have real partitioning then restoring such a dump will not create real partitions, just inherited tables. Perhaps we need some kind of option to reverse-engineer partitioning commands from the inheritance structure, but I fear having pg_dump reverse engineer inherited tables to produce partitioning commands will be too hard and error-prone. Hopefully that's too pessimistic though, if they were produced by PARTITION commands they should be pretty regular. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |
