Syntax for partitioning
in [PgSql]
|
Prev: [HACKERS] pg_read_file() and non-ascii input file
Next: [HACKERS] Proposal - temporal contrib module
From: Andres Freund on 29 Oct 2009 13:57 On Thursday 29 October 2009 18:33:22 Greg Stark wrote: > 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. One could have a system catalog containing the partitioning information and generate the constraints et al. from that and mark them in pg_depend... Andres -- 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: Peter Eisentraut on 29 Oct 2009 18:10 On tor, 2009-10-29 at 11:15 +0900, Itagaki Takahiro wrote: > 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 > ); Maybe this needs to mention the actual operator name instead of LESS THAN, in case the operator is not named < or the user wants to use a different one. > 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, > ... > ); Unless someone comes up with a maintenance plan for stable hash functions, we should probably not dare look into this yet. -- 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: Jeff Davis on 29 Oct 2009 18:19 On Fri, 2009-10-30 at 00:10 +0200, Peter Eisentraut wrote: > On tor, 2009-10-29 at 11:15 +0900, Itagaki Takahiro wrote: > > 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 > > ); > > Maybe this needs to mention the actual operator name instead of LESS > THAN, in case the operator is not named < or the user wants to use a > different one. I can't help but wonder if the PERIOD type might be better for representing a partition range. It would make it easier to express and enforce the constraint that no two partition ranges overlap ;) Regards, Jeff Davis -- 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: Itagaki Takahiro on 29 Oct 2009 21:51 Heikki Linnakangas <heikki.linnakangas(a)enterprisedb.com> 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. CREATE TABLE does not require PARTITION to be a reserved keyword, but there are conflicts in ALTER TABLE ADD/DROP PARTITION: * ALTER TABLE ... DROP [COLUMN] name [CASCADE | RESTRICT] * ALTER TABLE ... DROP PARTITION name [CASCADE | RESTRICT] There are some solutions: 1. Change COLUMN not to an optional word (unlikely) 2. Change syntax of DROP PARTITION to DROP TABLE PARITION or so 3. Change ALTER TABLE ADD/DROP PARTITION to top level => CREATE/DROP PARTITION name ON table_name Any better ideas? 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
From: Robert Haas on 29 Oct 2009 22:14
On Thu, Oct 29, 2009 at 9:51 PM, Itagaki Takahiro <itagaki.takahiro(a)oss.ntt.co.jp> wrote: > > Heikki Linnakangas <heikki.linnakangas(a)enterprisedb.com> 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. > > CREATE TABLE does not require PARTITION to be a reserved keyword, > but there are conflicts in ALTER TABLE ADD/DROP PARTITION: > > * ALTER TABLE ... DROP [COLUMN] name [CASCADE | RESTRICT] > * ALTER TABLE ... DROP PARTITION name [CASCADE | RESTRICT] > > There are some solutions: > > 1. Change COLUMN not to an optional word (unlikely) > 2. Change syntax of DROP PARTITION to DROP TABLE PARITION or so > 3. Change ALTER TABLE ADD/DROP PARTITION to top level > => CREATE/DROP PARTITION name ON table_name > > Any better ideas? I'm not sure if this is better, but what about: CREATE PARTITION name ON TABLE name DROP PARTITION name Since partitions will live in pg_class and are in some sense "top level" objects, it seems like it would make sense to use a syntax that is similar to the one we use for indices... we can't say "DROP COLUMN name", because the table must be specified. But a partition name must be unambiguous, so making the user write it out explicitly doesn't seem friendly. ....Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |