From: | Itagaki Takahiro <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: Syntax for partitioning |
Date: | 2009-11-05 11:43:36 |
Message-ID: | 20091105204336.AF0B.52131E4D@oss.ntt.co.jp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
Attachment | Content-Type | Size |
---|---|---|
partitioning_20091105.patch | application/octet-stream | 102.3 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Rafael Martinez | 2009-11-05 13:49:30 | ident changes between 8.3 and 8.4 |
Previous Message | Roberto Mello | 2009-11-05 11:42:01 | Re: Shall we just get rid of plpgsql's RENAME? |