Re: Syntax for partitioning - Mailing list 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