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.