[PATCH] Automatic HASH and LIST partition creation

On 14.07.2020 00:11, Anastasia Lubennikova wrote:
> 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