Add SPLIT PARTITION/MERGE PARTITIONS commands

Hi, hackers!

There are not many commands in PostgreSQL for working with partitioned 
tables. This is an obstacle to their widespread use.
Adding SPLIT PARTITION/MERGE PARTITIONS operations can make easier to 
use partitioned tables in PostgreSQL.
(This is especially important when migrating projects from ORACLE DBMS.)

SPLIT PARTITION/MERGE PARTITIONS commands are supported for range 
partitioning (BY RANGE) and for list partitioning (BY LIST).
For hash partitioning (BY HASH) these operations are not supported.

=================
1 SPLIT PARTITION
=================
Command for split a single partition.

1.1 Syntax
----------

ALTER TABLE <name> SPLIT PARTITION <partition_name> INTO
(PARTITION <partition_name1> { FOR VALUES <partition_bound_spec> | 
DEFAULT },
   [ ... ]
   PARTITION <partition_nameN> { FOR VALUES <partition_bound_spec> | 
DEFAULT })

<partition_bound_spec>:
    IN ( <partition_bound_expr> [, ...] ) |
    FROM ( { <partition_bound_expr> | MINVALUE | MAXVALUE } [, ...] )
    TO ( { <partition_bound_expr> | MINVALUE | MAXVALUE } [, ...] )

1.2 Rules
---------

1.2.1 The <partition_name> partition should be split into two (or more) 
partitions.

1.2.2 New partitions should have different names (with existing 
partitions too).

1.2.3 Bounds of new partitions should not overlap with new and existing 
partitions.

1.2.4 In case split partition is DEFAULT partition, one of new 
partitions should be DEFAULT.

1.2.5 In case new partitions or existing partitions contains DEFAULT 
partition, new partitions <partition_name1>...<partition_nameN> can have 
any bounds inside split partition bound (can be spaces between 
partitions bounds).

1.2.6 In case partitioned table does not have DEFAULT partition, DEFAULT 
partition can be defined as one of new partition.

1.2.7 In case new partitions not contains DEFAULT partition and 
partitioned table does not have DEFAULT partition the following should 
be true: sum bounds of new partitions 
<partition_name1>...<partition_nameN> should be equal to bound of split 
partition <partition_name>.

1.2.8 One of the new partitions <partition_name1>-<partition_nameN> can 
have the same name as split partition <partition_name> (this is suitable 
in case splitting a DEFAULT partition: we split it, but after splitting 
we have a partition with the same name).

1.2.9 Only simple (non-partitioned) partitions can be split.

1.3 Examples
------------

1.3.1 Example for range partitioning (BY RANGE):

CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), 
sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_jan2022 PARTITION OF sales_range FOR VALUES FROM 
('2022-01-01') TO ('2022-02-01');
CREATE TABLE sales_feb_mar_apr2022 PARTITION OF sales_range FOR VALUES 
FROM ('2022-02-01') TO ('2022-05-01');
CREATE TABLE sales_others PARTITION OF sales_range DEFAULT;

ALTER TABLE sales_range SPLIT PARTITION sales_feb_mar_apr2022 INTO
    (PARTITION sales_feb2022 FOR VALUES FROM ('2022-02-01') TO 
('2022-03-01'),
     PARTITION sales_mar2022 FOR VALUES FROM ('2022-03-01') TO 
('2022-04-01'),
     PARTITION sales_apr2022 FOR VALUES FROM ('2022-04-01') TO 
('2022-05-01'));

1.3.2 Example for list partitioning (BY LIST):

CREATE TABLE sales_list
    (salesman_id INT GENERATED ALWAYS AS IDENTITY,
     salesman_name VARCHAR(30),
     sales_state VARCHAR(20),
     sales_amount INT,
     sales_date DATE)
PARTITION BY LIST (sales_state);

CREATE TABLE sales_nord PARTITION OF sales_list FOR VALUES IN 
('Murmansk', 'St. Petersburg', 'Ukhta');
CREATE TABLE sales_all PARTITION OF sales_list FOR VALUES IN ('Moscow', 
'Voronezh', 'Smolensk', 'Bryansk', 'Magadan', 'Kazan', 'Khabarovsk', 
'Volgograd', 'Vladivostok');
CREATE TABLE sales_others PARTITION OF sales_list DEFAULT;

ALTER TABLE sales_list SPLIT PARTITION sales_all INTO
    (PARTITION sales_west FOR VALUES IN ('Voronezh', 'Smolensk', 'Bryansk'),
     PARTITION sales_east FOR VALUES IN ('Magadan', 'Khabarovsk', 
'Vladivostok'),
     PARTITION sales_central FOR VALUES IN ('Moscow', 'Kazan', 
'Volgograd'));

1.4 ToDo:
---------

1.4.1 Possibility to specify tablespace for each of the new partitions 
(currently new partitions are created in the same tablespace as split 
partition).
1.4.2 Possibility to use CONCURRENTLY mode that allows (during the SPLIT 
operation) not blocking partitions that are not splitting.

==================
2 MERGE PARTITIONS
==================
Command for merge several partitions into one partition.

2.1 Syntax
----------

ALTER TABLE <name> MERGE PARTITIONS (<partition_name1>, 
<partition_name2>[, ...]) INTO <new_partition_name>;

2.2 Rules
---------

2.2.1 The number of partitions that are merged into the new partition 
<new_partition_name> should be at least two.

2.2.2
If DEFAULT partition is not in the list of partitions <partition_name1>, 
<partition_name2>[, ...]:
   * for range partitioning (BY RANGE) is necessary that the ranges of 
the partitions <partition_name1>, <partition_name2>[, ...] can be merged 
into one range without spaces and overlaps (otherwise an error will be 
generated).
     The combined range will be the range for the partition 
<new_partition_name>.
   * for list partitioning (BY LIST) the values lists of all partitions 
<partition_name1>, <partition_name2>[, ...] are combined and form a list 
of values of partition <new_partition_name>.

If DEFAULT partition is in the list of partitions <partition_name1>, 
<partition_name2>[, ...]:
   * the partition <new_partition_name> will be the DEFAULT partition;
   * for both partitioning types (BY RANGE, BY LIST) the ranges and 
lists of values of the merged partitions can be any.

2.2.3 The new partition <new_partition_name> can have the same name as 
one of the merged partitions.

2.2.4 Only simple (non-partitioned) partitions can be merged.

2.3 Examples
------------

2.3.1 Example for range partitioning (BY RANGE):

CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), 
sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_jan2022 PARTITION OF sales_range FOR VALUES FROM 
('2022-01-01') TO ('2022-02-01');
CREATE TABLE sales_feb2022 PARTITION OF sales_range FOR VALUES FROM 
('2022-02-01') TO ('2022-03-01');
CREATE TABLE sales_mar2022 PARTITION OF sales_range FOR VALUES FROM 
('2022-03-01') TO ('2022-04-01');
CREATE TABLE sales_apr2022 PARTITION OF sales_range FOR VALUES FROM 
('2022-04-01') TO ('2022-05-01');
CREATE TABLE sales_others PARTITION OF sales_range DEFAULT;

ALTER TABLE sales_range MERGE PARTITIONS (sales_feb2022, sales_mar2022, 
sales_apr2022) INTO sales_feb_mar_apr2022;

2.3.2 Example for list partitioning (BY LIST):

CREATE TABLE sales_list
(salesman_id INT GENERATED ALWAYS AS IDENTITY,
   salesman_name VARCHAR(30),
   sales_state VARCHAR(20),
   sales_amount INT,
   sales_date DATE)
PARTITION BY LIST (sales_state);

CREATE TABLE sales_nord PARTITION OF sales_list FOR VALUES IN 
('Murmansk', 'St. Petersburg', 'Ukhta');
CREATE TABLE sales_west PARTITION OF sales_list FOR VALUES IN 
('Voronezh', 'Smolensk', 'Bryansk');
CREATE TABLE sales_east PARTITION OF sales_list FOR VALUES IN 
('Magadan', 'Khabarovsk', 'Vladivostok');
CREATE TABLE sales_central PARTITION OF sales_list FOR VALUES IN 
('Moscow', 'Kazan', 'Volgograd');
CREATE TABLE sales_others PARTITION OF sales_list DEFAULT;

ALTER TABLE sales_list MERGE PARTITIONS (sales_west, sales_east, 
sales_central) INTO sales_all;

2.4 ToDo:
---------

2.4.1 Possibility to specify tablespace for the new partition (currently 
new partition is created in the same tablespace as partitioned table).
2.4.2 Possibility to use CONCURRENTLY mode that allows (during the MERGE 
operation) not blocking partitions that are not merging.
2.4.3 New syntax for ALTER TABLE ... MERGE PARTITIONS command for range 
partitioning (BY RANGE):

ALTER TABLE <name> MERGE PARTITIONS <partition_name1> TO 
<partition_name2> INTO <new_partition_name>;

This command can merge all partitions between <partition_name1> and
<partition_name2> into new partition <new_partition_name>.
This can be useful for this example cases: need to merge all one-month 
partitions into a year partition or need to merge all one-day partitions 
into a month partition.

Your opinions are very much welcome!

-- 
With best regards,
Dmitry Koval.