Re: On partitioning - Mailing list pgsql-hackers

On Tue, Nov 25, 2014 at 8:20 PM, Amit Langote
<Langote_Amit_f8@lab.ntt.co.jp> wrote:
>> Before going too much further with this I'd mock up schemas for your
>> proposed catalogs and a list of DDL operations to be supported, with
>> the corresponding syntax, and float that here for comment.

More people should really comment on this.  This is a pretty big deal
if it goes forward, so it shouldn't be based on what one or two people
think.

> * Catalog schema:
>
> CREATE TABLE pg_catalog.pg_partitioned_rel
> (
>    partrelid                oid    NOT NULL,
>    partkind                oid    NOT NULL,
>    partissub              bool  NOT NULL,
>    partkey                 int2vector NOT NULL, -- partitioning attributes
>    partopclass         oidvector,
>
>    PRIMARY KEY (partrelid, partissub),
>    FOREIGN KEY (partrelid)   REFERENCES pg_class (oid),
>    FOREIGN KEY (partopclass) REFERENCES pg_opclass (oid)
> )
> WITHOUT OIDS ;

So, we're going to support exactly two levels of partitioning?
partitions with partissub=false and subpartitions with partissub=true?Why not support only one level of partitioning
herebut then let the
 
children have their own pg_partitioned_rel entries if they are
subpartitioned?  That seems like a cleaner design and lets us support
an arbitrary number of partitioning levels if we ever need them.

> CREATE TABLE pg_catalog.pg_partition_def
> (
>    partitionid                      oid     NOT NULL,
>    partitionparentrel       oid    NOT NULL,
>    partitionisoverflow     bool  NOT NULL,
>    partitionvalues             anyarray,
>
>    PRIMARY KEY (partitionid),
>    FOREIGN KEY (partitionid) REFERENCES pg_class(oid)
> )
> WITHOUT OIDS;
>
> ALTER TABLE pg_catalog.pg_class ADD COLUMN relispartitioned;

What is an overflow partition and why do we want that?

What are you going to do if the partitioning key has two columns of
different data types?

> * DDL syntax (no multi-column partitioning, sub-partitioning support as yet):
>
> -- create partitioned table and child partitions at once.
> CREATE TABLE parent (...)
> PARTITION BY [ RANGE | LIST ] (key_column) [ opclass ]
> [ (
>      PARTITION child
>        {
>            VALUES LESS THAN { ... | MAXVALUE } -- for RANGE
>          | VALUES [ IN ] ( { ... | DEFAULT } ) -- for LIST
>        }
>        [ WITH ( ... ) ] [ TABLESPACE tbs ]
>      [, ...]
>   ) ] ;

How are you going to dump and restore this, bearing in mind that you
have to preserve a bunch of OIDs across pg_upgrade?  What if somebody
wants to do pg_dump --table name_of_a_partition?

I actually think it will be much cleaner to declare the parent first
and then have separate CREATE TABLE statements that glue the children
in, like CREATE TABLE child PARTITION OF parent VALUES LESS THAN (1,
10000).

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company