Re: Add SPLIT PARTITION/MERGE PARTITIONS commands

Hello!

Added commit v21-0004-SPLIT-PARTITION-optimization.patch.

Three already existing commits did not change
(v21-0001-ALTER-TABLE-MERGE-PARTITIONS-command.patch,
v21-0002-ALTER-TABLE-SPLIT-PARTITION-command.patch,
v21-0003-Documentation-for-ALTER-TABLE-SPLIT-PARTITION-ME.patch).

The new commit is an optimization for the SPLIT PARTITION command.

Description of optimization:
1) optimization is used for the SPLIT PARTITION command for tables with
BY RANGE partitioning in case the partitioning key has a b-tree index;
2) the point of optimization is that, if after dividing of the old
partition, all its records according to the range conditions must be
inserted into ONE new partition, then instead of transferring data (from
the old partition to new partition), the old partition will be renamed.

Example.
Suppose we have a BY RANGE-partitioned table "test" (indexed by
partitioning key) with a single partition "test_default", which we want
to split into two partitions ("test_1" and "test_default"), and all
records should be moved to the "test_1" partition.
When executing the script below, the "test_default" partition will be
renamed to "test_1".

----
CREATE TABLE test(d date, v text) PARTITION BY RANGE (d);
CREATE TABLE test_default PARTITION OF test DEFAULT;

CREATE INDEX idx_test_d ON test USING btree (d);

INSERT INTO test (d, v)
SELECT d, 'value_' || md5(random()::text) FROM
generate_series('2024-01-01', '2024-01-25', interval '10 seconds')
AS d;

-- Oid of table 'test_default':
SELECT 'test_default'::regclass::oid AS previous_partition_oid;

ALTER TABLE test SPLIT PARTITION test_default INTO
(PARTITION test_1 FOR VALUES FROM ('2024-01-01') TO ('2024-02-01'),
PARTITION test_default DEFAULT);

-- Oid of table 'test_1' (should be the same as "previous_partition_oid"):
SELECT 'test_1'::regclass::oid AS current_partition_oid;

DROP TABLE test CASCADE;

--
With best regards,
Dmitry Koval

Postgres Professional: http://postgrespro.com