Re: Syntax for partitioning

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

In response to

Responses

Browse pgsql-hackers by date

  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