Syntax for partitioning
in [PgSql]
|
Prev: [HACKERS] pg_read_file() and non-ascii input file
Next: [HACKERS] Proposal - temporal contrib module
From: Emmanuel Cecchet on 24 Nov 2009 22:43 Hi, Sorry for commenting only now but I think that we need to be able to store the partitions in different tablespaces. Even if originally the create table creates all partitions in the same tablespace, individual partitions should be allowed to be moved in different tablespaces using alter table or alter partition. I think that other databases allows the user to define a tablespace for each partition in the create table statement. In a warehouse, you might want to split your partitions on different volumes and over time, move older partitions to storage with higher compression if that data is not to be accessed frequently anymore. Altering tablespaces for partitions is important in that context. Are you also planning to provide partitioning extensions to 'create table as'? Thanks Emmanuel > Here is a WIP partitioning patch. The new syntax are: > 1. CREATE TABLE parent (...); > 2. ALTER TABLE parent PARTITION BY { RANGE | LIST } ( key ); > 3. CREATE TABLE child (...); > 4. ALTER TABLE child INHERIT parent AS PARTITION VALUES ...; > > We can also use "CREATE TABLE PARTITION BY" as 1+2+3+4 and > "CREATE PARTITION" as 3+4. I think "INHERIT AS PARTITION" is rarely > used typically, but such orthogonality seems to be cleaner. > > The most complex logic of the patch is in ATExecAddInherit(). It scans > existing partitions and generate CHECK constraint for the new partition. > > Any comments to the design? If no objections, I'd like to stop adding > features in this CommitFest and go for remaining auxiliary works > -- pg_dump, object dependency checking, documentation, etc. > > >> ----------------- >> Catalog changes >> ----------------- >> > In addition to pg_partition, I added pg_inherits.inhvalues field. > The type of field is "anyarray" and store partition values. > For range partition, an upper bound value is stored in the array. > For list partition, list values are stored in it. These separated > value fields will be useful to implement partition triggers in the > future. In contrast, reverse engineering of check constraints is messy. > > CATALOG(pg_inherits,2611) BKI_WITHOUT_OIDS > { > Oid inhrelid; > Oid inhparent; > int4 inhseqno; > anyarray inhvalues; /* values for partition */ > } FormData_pg_inherits; > > >> CREATE TABLE pg_partition ( >> partrelid oid REFERENCES oid ON pg_class, -- partitioned table oid >> partopr oid REFERENCES oid ON pg_operator, -- operator to compare keys >> partkind "char", -- kind of partition: 'R' (range) or 'L' (list) >> partkey text, -- expression tree of partition key >> PRIMARY KEY (partrelid) >> ) WITHOUT OIDS; >> > > ------------------------------ > Limitations and Restrictions > ------------------------------ > * We can create a new partition as long as partitioning keys > are not conflicted with existing partitions. Especially, > we cannot add any partitions if we have overflow partitions > because a new partition always split the overflow partition. > > * We cannot reuse an existing check constraint as a partition > constraint. ALTER TABLE INHERIT AS PARTITION brings on > a table scan to add a new CHECK constraint. > > * No partition triggers nor planner and executor improvements. > It would come in the future development. > > Regards, > --- > ITAGAKI Takahiro > NTT Open Source Software Center > > > ------------------------------------------------------------------------ > > > -- Emmanuel Cecchet Aster Data Web: http://www.asterdata.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: Itagaki Takahiro on 24 Nov 2009 23:01 Emmanuel Cecchet <manu(a)asterdata.com> wrote: > I think that other databases allows the > user to define a tablespace for each partition in the create table > statement. WITH and TABLESPACE clause are supported for each partition. =# CREATE TABLE parent (...) PARTITION BY (key) ( PARTITION child_1 VALUES LESS THAN 10 WITH (...) TABLESPACE tbs_1 ); =# CREATE PARTITION child_2 ON parent VALUES LESS THAN 20 WITH (...) TABLESPACE tbl_2; > Are you also planning to provide partitioning extensions to 'create > table as'? Ah, I forgot that. It would be possible to have the feature. There are no syntax issues. But it would be done after we support automatic INSERT routing. We can create the table will partitions, but tuples are not divided into child partitions because we have no insert-triggers at the time of CREATE TABLE AS. 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: Greg Smith on 24 Nov 2009 23:52 I just made a few updates to http://wiki.postgresql.org/wiki/Table_partitioning , merging in the stuff that had been on the ToDo page and expanding the links to discussion on this list a bit. The number of submitted patches over the last couple of years that handle some subset of the desired feature set here is really remarkable when you see them all together. -- Greg Smith 2ndQuadrant Baltimore, MD PostgreSQL Training, Services and Support greg(a)2ndQuadrant.com www.2ndQuadrant.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: Emmanuel Cecchet on 25 Nov 2009 00:37 Greg Smith wrote: > I just made a few updates to > http://wiki.postgresql.org/wiki/Table_partitioning , merging in the > stuff that had been on the ToDo page and expanding the links to > discussion on this list a bit. The number of submitted patches over > the last couple of years that handle some subset of the desired > feature set here is really remarkable when you see them all together. > Should we add the 'WITH (...) TABLESPACE tbs' options to the syntax since they are supported? Do we support ALTER ... SET TABLESPACE? Emmanuel -- Emmanuel Cecchet Aster Data Web: http://www.asterdata.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: Itagaki Takahiro on 25 Nov 2009 01:03
Emmanuel Cecchet <manu(a)asterdata.com> wrote: > Should we add the 'WITH (...) TABLESPACE tbs' options to the syntax > since they are supported? Added the description. > Do we support ALTER ... SET TABLESPACE? DROP/ALTER PARTITION are synonyms for DROP/ALTER TABLE. SET TABLESPACE is also supported. Added the description. 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 |