[PATCH] Automatic HASH and LIST partition creation

On 06.07.2020 13:45, Anastasia Lubennikova wrote:

The previous discussion of automatic partition creation [1] has addressed static and dynamic creation of partitions and ended up with several syntax proposals.
In this thread, I want to continue this work.

...
[1] https://www.postgresql.org/message-id/flat/alpine.DEB.2.21.1907150711080.22273%40lancre

Syntax proposal v2, that takes into account received feedback.

CREATE TABLE numbers(int number)
PARTITION BY partition_method (list_of_columns)
USING (partition_desc)

where partition_desc is:

MODULUS n
| VALUES IN (value_list), [DEFAULT PARTITION part_name]
| START ([datatype] 'start_value')  END ([datatype] 'end_value')  EVERY (partition_step), [DEFAULT PARTITION part_name]

where partition_step is:
[datatype] [number | INTERVAL] 'interval_value'

It is less wordy than the previous version. It uses a free keyword option
style. It covers static partitioning for all methods, default partition for
list and range methods, and can be extended to implement dynamic partitioning
for range partitions.

[1] https://wiki.postgresql.org/wiki/Declarative_partitioning_improvements#Other_DBMS
[2] https://wiki.postgresql.org/wiki/Declarative_partitioning_improvements#Proposal_.28is_subject_to_change.29

Here is the patch for automated HASH and LIST partitioning, that implements proposed syntax.

Range partitioning is more complicated. It will require new support function to calculate bounds, new catalog attribute to store them and so on. So I want to start small and implement automated range partitioning in a separate patch later.

1) Syntax

New syntax is heavily based on Greenplum syntax for automated partitioning with one change. Keyword "USING", that was suggested above, causes shift/reduce conflict with "USING method" syntax of a table access method. It seems that Greenplum folks will face this problem later.

I stick to CONFIGURATION as an existing keyword that makes sense in this context.
Any better ideas are welcome.

Thus, current version is:

CREATE TABLE table_name (attrs)
PARTITION BY partition_method (list_of_columns)
CONFIGURATION (partition_desc)

where partition_desc is:

MODULUS n
| VALUES IN (value_list) [DEFAULT PARTITION part_name]

This syntax can be easily extended for range partitioning as well.

2) Implementation

PartitionBoundAutoSpec is a new part of PartitionSpec, that contains information needed to generate partition bounds.

For HASH and LIST automatic partition creation, transformation happens during parse analysis of CREATE TABLE statement.
transformPartitionAutoCreate() calculates bounds and generates statements to create partition tables.

Partitions are named in a format: $tablename_$partnum. One can use post create hook to rename relations.

For LIST partition one can also define a default partition.

3) TODO

The patch lacks documentation, because I expect some details may change during discussion. Other than that, the feature is ready for review.


Regards

-- 
Anastasia Lubennikova
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company