Re: Declarative partitioning

From: Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Declarative partitioning
Date: 2016-02-15 01:55:57
Message-ID: 56C1302D.6070403@lab.ntt.co.jp
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

Hi,

Attached find a patch series to implement the following syntax:

1. Syntax for defining the partition key as a new clause in CREATE TABLE:

[ PARTITION BY {RANGE | LIST} ON ( { column_name | ( expression ) } [
opclass ] [, ...] )
[ SUBPARTITION BY {RANGE | LIST} ON ( { column_name | ( expression ) }
[ opclass ] [, ...] ) ] ]

2. Syntax to create partitions and sub-partitions (as ALTER TABLE commands
on a partitioned table):

ADD PARTITION name FOR VALUES partition_bound_spec
[ WITH ( storage_parameter [= value] [, ... ] )]
[ TABLESPACE tablespace_name ]

MODIFY PARTITION partition_name ADD SUBPARTITION name FOR VALUES
partition_bound_spec
[ WITH ( storage_parameter [= value] [, ... ] )]
[ TABLESPACE tablespace_name ]

DROP PARTITION name
DROP SUBPARTITION name

ATTACH PARTITION name FOR VALUES partition_bound_spec
USING [ TABLE ] table_name
[ WITH ( storage_parameter [= value] [, ... ] )]
[ TABLESPACE tablespace_name ]

MODIFY PARTITION partition_name ATTACH SUBPARTITION name FOR VALUES
partition_bound_spec
USING [ TABLE ] table_name
[ WITH ( storage_parameter [= value] [, ... ] )]
[ TABLESPACE tablespace_name ]

Where partition_bound_spec is:

FOR VALUES [ IN ] ( expression [, ...] )
FOR VALUES LESS THAN ( expression [, ...] )

DETACH PARTITION name [ USING [ TABLE ] table_name]
DETACH SUBPARTITION name [ USING [ TABLE ] table_name]

Please note that ATTACH PARTITION ... USING TABLE command shown above is
not yet implemented.

As mentioned previously, this uses two system catalogs pg_partitioned_rel
and pg_partition.

This is complete with tuple routing so that no triggers or rules are
required. There is also basic planner support but no support yet to enable
constraint exclusion on partitions (which will be fulfilled shortly by
installing equivalent check constraints on partitions).

There is documentation for the new syntax and catalogs. I feel more
detailed documentation is required so I will keep on improving it in
subsequent versions as we build consensus about the syntax and general
design. There are no regression tests at all in the attached patches which
I will add in the next version along with constraint exclusion support.
pg_dump support will also be added shortly.

The individual patches have commit messages that describe code changes.
This is registered in the upcoming CF. Feedback and review is greatly
welcomed!

Thanks,
Amit

Attachment Content-Type Size
0001-Add-relkind-RELKIND_PARTITIONED_REL-to-pg_class.h.patch text/x-diff 940 bytes
0002-Add-partitioned-table-system-catalog.patch text/x-diff 11.0 KB
0003-Introduce-partition-key-clause-in-CREATE-TABLE.patch text/x-diff 22.0 KB
0004-Infrastructure-for-creation-of-partitioned-tables.patch text/x-diff 79.9 KB
0005-Add-partition-system-catalog.patch text/x-diff 8.3 KB
0006-Add-partition-creation-management-syntax.patch text/x-diff 39.6 KB
0007-Add-infrastructure-to-actually-create-and-manage-par.patch text/x-diff 81.0 KB
0008-Rework-code-manipulating-the-PartitionKey-data-struc.patch text/x-diff 34.0 KB
0009-Tuple-routing-functionality.patch text/x-diff 40.7 KB
0010-Embarrasingly-small-optimizer-patch-to-work-with-par.patch text/x-diff 13.7 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2016-02-15 01:58:17 Re: WIP: SCRAM authentication
Previous Message Stephen Frost 2016-02-15 01:53:06 Re: WIP: SCRAM authentication