Re: On partitioning

On Tue, Dec 2, 2014 at 8:53 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
> On Tue, Nov 25, 2014 at 8:20 PM, Amit Langote
> <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)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 here but 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?
>

Do we really need to support dml or pg_dump for individual partitions?

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com