Re: Syntax for partitioning
От | Emmanuel Cecchet |
---|---|
Тема | Re: Syntax for partitioning |
Дата | |
Msg-id | 4B0CA7F4.5010206@asterdata.com обсуждение исходный текст |
Ответ на | Re: Syntax for partitioning (Itagaki Takahiro <itagaki.takahiro@oss.ntt.co.jp>) |
Ответы |
Re: Syntax for partitioning
|
Список | pgsql-hackers |
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
В списке pgsql-hackers по дате отправления: