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-12 10:54:50 |
Message-ID: | 20091112195450.A967.52131E4D@oss.ntt.co.jp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
I added psql and pg_dump support to Partitioning Syntax patch.
Paritioning information can be retrieved with a new system function
pg_get_partitiondef(parentRelid). Both psql and pg_dump use it.
There are some changes from the last patch.
Some of them seem to be a bit ugly. Ideas welcome.
* If a table with the same name already exists when a partition
is created, the table is re-used as partition. This behavior
is required for pg_dump to be simple.
* Don't create a new check constraint when a table is attached as
partition to a parent table if the child table has constraints
with the same definition. This behavior is required for pg_dump
not to add duplicated check constraints in repeated dump and restore.
* Inheritance is used for partitions, but pg_dump doesn't dump them as
inheritance; It dump a child table without inheritance first, and
re-add inheritance with ALTER TABLE PARTITION BY. PartitionInfo is
added as a DumpableObject in pg_dump.
* Dependencies of objects are managed with existing depencency manager
except a check constraint to partition values. Partition constraints
can be dropped even if the table is still in the partitioning set.
A patch attached, and I'll summarize it:
==== Syntax ====
CREATE TABLE parent (...)
PARTITION BY { RANGE | LIST } ( key [ USING oprator ] )
( <partitions> );
ALTER TABLE parent PARTITION BY { RANGE | LIST } ...;
CREATE PARTITION partition ON parent VALUES ...;
<partitions> :=
PARTITION name VALUES LESS THAN { range_upper | MAXVALUE }
| PARTITION name VALUES IN ( list_value [,...] | DEFAULT )
==== System Catalog ====
CREATE TABLE pg_partition (
partrelid oid UNIQUE REFARENCES pg_class(oid),
partopr oid REFARENCES pg_operatoroid),
partkind "char", -- 'R':RANGE or 'L':LIST
partkey text -- node dump of the partition key
) WITHOUT OIDS;
CREATE TABLE pg_inherits (
inhrelid oid,
inhparent oid,
inhseqno integer,
+ inhvalues anyarray -- Non-null if the inheritance is for partitioning.
) WITHOUT OIDS;
==== Sample output from psql ====
=# \d sales_range
Table "public.sales_range"
Column | Type | Modifiers
---------------+-----------------------------+-----------
salesman_id | numeric(5,0) |
salesman_name | character varying(30) |
sales_state | character varying(20) |
sales_date | timestamp without time zone |
Partitions: PARTITION BY RANGE ( sales_date USING < )
(
PARTITION sales_2006 VALUES LESS THAN '2007-01-01 00:00:00',
...
)
==== Sample output from pg_dump ====
CREATE TABLE sales_range (...);
CREATE TABLE sales_2006 (...); -- without inheritance
ALTER TABLE public.sales_range PARTITION BY RANGE ( sales_date USING < )
(
PARTITION sales_2006 VALUES LESS THAN '2007-01-01 00:00:00',
...
);
Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center
Attachment | Content-Type | Size |
---|---|---|
partitioning_20091112.patch | application/octet-stream | 136.2 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Dimitri Fontaine | 2009-11-12 11:03:59 | Re: Patch committers |
Previous Message | Joachim Wieland | 2009-11-12 10:21:01 | Re: Listen / Notify rewrite |