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