Re: On partitioning - Mailing list pgsql-hackers

On Tue, Dec 2, 2014 at 8:53 PM, Robert Haas <robertmhaas@gmail.com> wrote:
>
> 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 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 ]
> >      [, ...]
> >   ) ] ;