Re: On partitioning
От | Amit Kapila |
---|---|
Тема | Re: On partitioning |
Дата | |
Msg-id | CAA4eK1JfxZtMaYwDp+hWec+tHDupr5rq3h30CfysO+QVisNBBQ@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: On partitioning (Robert Haas <robertmhaas@gmail.com>) |
Ответы |
Re: On partitioning
(Robert Haas <robertmhaas@gmail.com>)
|
Список | 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 ]
> > [, ...]
> > ) ] ;
>
> 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 ]
> > [, ...]
> > ) ] ;
>
> 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?
>
> 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?
В списке pgsql-hackers по дате отправления:
Предыдущее
От: Fujii MasaoДата:
Сообщение: Re: pg_basebackup -x/X doesn't play well with archive_mode & wal_keep_segments