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.
Attachments:
v1-0001-partitions-split-merge.patchtext/plain; charset=UTF-8; name=v1-0001-partitions-split-merge.patchDownload+5318-29
Import Notes
Reply to msg id not found: 163714495450.16056.3566953095730878367@malur.postgresql.orgReference msg id not found: 163714495450.16056.3566953095730878367@malur.postgresql.org
On Tue, 31 May 2022 at 11:33, Dmitry Koval <d.koval@postgrespro.ru> wrote:
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.
That is quite a nice and useful feature to have.
(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.
Just out of curiosity, why is SPLIT / MERGE support not included for
HASH partitions? Because sibling partitions can have a different
modulus, you should be able to e.g. split a partition with (modulus,
remainder) of (3, 1) into two partitions with (mod, rem) of (6, 1) and
(6, 4) respectively, with the reverse being true for merge operations,
right?
Kind regards,
Matthias van de Meent
On Tue, 2022-05-31 at 12:32 +0300, Dmitry Koval wrote:
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 on the general idea.
At least, it will makes these operations simpler, but probably also less
invasive (no need to detach the affected partitions).
I didn't read the patch, but what lock level does that place on the
partitioned table? Anything more than ACCESS SHARE?
Yours,
Laurenz Albe
Just out of curiosity, why is SPLIT / MERGE support not included for
HASH partitions? Because sibling partitions can have a different
modulus, you should be able to e.g. split a partition with (modulus,
remainder) of (3, 1) into two partitions with (mod, rem) of (6, 1) and
(6, 4) respectively, with the reverse being true for merge operations,
right?
You are right, SPLIT/MERGE operations can be added for HASH-partitioning
in the future. But HASH-partitioning is rarer than RANGE- and
LIST-partitioning and I decided to skip it in the first step.
Maybe community will say that SPLIT/MERGE commands are not needed... (At
first step I would like to make sure that it is no true)
P.S. I attached patch with 1-line warning fix (for cfbot).
--
With best regards,
Dmitry Koval
Postgres Professional: http://postgrespro.com
Attachments:
v2-0001-partitions-split-merge.patchtext/plain; charset=UTF-8; name=v2-0001-partitions-split-merge.patchDownload+5318-29
I didn't read the patch, but what lock level does that place on the
partitioned table? Anything more than ACCESS SHARE?
Current patch locks a partitioned table with ACCESS EXCLUSIVE lock.
Unfortunately only this lock guarantees that other session can not work
with partitions that are splitting or merging.
I want add CONCURRENTLY mode in future. With this mode partitioned table
during SPLIT/MERGE operation will be locked with SHARE UPDATE EXCLUSIVE
(as ATTACH/DETACH PARTITION commands in CONCURRENTLY mode).
But in this case queries from other sessions that want to work with
partitions that are splitting/merging at this time should receive an
error (like "Partition data is moving. Repeat the operation later")
because old partitions will be deleted at the end of SPLIT/MERGE operation.
I hope exists a better solution, but I don't know it now...
--
With best regards,
Dmitry Koval
Postgres Professional: http://postgrespro.com
On Tue, May 31, 2022 at 12:43 PM Dmitry Koval <d.koval@postgrespro.ru>
wrote:
Just out of curiosity, why is SPLIT / MERGE support not included for
HASH partitions? Because sibling partitions can have a different
modulus, you should be able to e.g. split a partition with (modulus,
remainder) of (3, 1) into two partitions with (mod, rem) of (6, 1) and
(6, 4) respectively, with the reverse being true for merge operations,
right?You are right, SPLIT/MERGE operations can be added for HASH-partitioning
in the future. But HASH-partitioning is rarer than RANGE- and
LIST-partitioning and I decided to skip it in the first step.
Maybe community will say that SPLIT/MERGE commands are not needed... (At
first step I would like to make sure that it is no true)P.S. I attached patch with 1-line warning fix (for cfbot).
--
With best regards,
Dmitry KovalPostgres Professional: http://postgrespro.com
Hi,
For attachPartTable, the parameter wqueue is missing from comment.
The parameters of CloneRowTriggersToPartition are called parent
and partition. I think it is better to name the parameters to
attachPartTable in a similar manner.
For struct SplitPartContext, SplitPartitionContext would be better name.
+ /* Store partition contect into list. */
contect -> context
Cheers
On Tue, May 31, 2022 at 1:43 PM Zhihong Yu <zyu@yugabyte.com> wrote:
On Tue, May 31, 2022 at 12:43 PM Dmitry Koval <d.koval@postgrespro.ru>
wrote:Just out of curiosity, why is SPLIT / MERGE support not included for
HASH partitions? Because sibling partitions can have a different
modulus, you should be able to e.g. split a partition with (modulus,
remainder) of (3, 1) into two partitions with (mod, rem) of (6, 1) and
(6, 4) respectively, with the reverse being true for merge operations,
right?You are right, SPLIT/MERGE operations can be added for HASH-partitioning
in the future. But HASH-partitioning is rarer than RANGE- and
LIST-partitioning and I decided to skip it in the first step.
Maybe community will say that SPLIT/MERGE commands are not needed... (At
first step I would like to make sure that it is no true)P.S. I attached patch with 1-line warning fix (for cfbot).
--
With best regards,
Dmitry KovalPostgres Professional: http://postgrespro.com
Hi,
For attachPartTable, the parameter wqueue is missing from comment.
The parameters of CloneRowTriggersToPartition are called parent
and partition. I think it is better to name the parameters to
attachPartTable in a similar manner.For struct SplitPartContext, SplitPartitionContext would be better name.
+ /* Store partition contect into list. */
contect -> contextCheers
Hi,
For transformPartitionCmdForMerge(), nested loop is used to detect
duplicate names.
If the number of partitions in partcmd->partlist, we should utilize map to
speed up the check.
For check_parent_values_in_new_partitions():
+ if (!find_value_in_new_partitions(&key->partsupfunc[0],
+ key->partcollation, parts,
nparts, datum, false))
+ found = false;It seems we can break out of the loop when found is false.
Cheers
Hi,
1)
For attachPartTable, the parameter wqueue is missing from comment.
The parameters of CloneRowTriggersToPartition are called parent and partition.
I think it is better to name the parameters to attachPartTable in a similar manner.For struct SplitPartContext, SplitPartitionContext would be better name.
+ /* Store partition contect into list. */
contect -> context
Thanks, changed.
2)
For transformPartitionCmdForMerge(), nested loop is used to detect duplicate names.
If the number of partitions in partcmd->partlist, we should utilize map to speed up the check.
I'm not sure what we should utilize map in this case because chance that
number of merging partitions exceed dozens is low.
Is there a function example that uses a map for such a small number of
elements?
3)
For check_parent_values_in_new_partitions():
+ if (!find_value_in_new_partitions(&key->partsupfunc[0], + key->partcollation, parts, nparts, datum, false)) + found = false;It seems we can break out of the loop when found is false.
We have implicit "break" in "for" construction:
+ for (i = 0; i < boundinfo->ndatums && found; i++)
I'll change it to explicit "break;" to avoid confusion.
Attached patch with the changes described above.
--
With best regards,
Dmitry Koval
Postgres Professional: http://postgrespro.com
Attachments:
v3-0001-partitions-split-merge.patchtext/plain; charset=UTF-8; name=v3-0001-partitions-split-merge.patchDownload+5323-29
On Wed, Jun 1, 2022 at 11:58 AM Dmitry Koval <d.koval@postgrespro.ru> wrote:
Hi,
1)
For attachPartTable, the parameter wqueue is missing from comment.
The parameters of CloneRowTriggersToPartition are called parent andpartition.
I think it is better to name the parameters to attachPartTable in a
similar manner.
For struct SplitPartContext, SplitPartitionContext would be better name.
+ /* Store partition contect into list. */
contect -> contextThanks, changed.
2)
For transformPartitionCmdForMerge(), nested loop is used to detect
duplicate names.
If the number of partitions in partcmd->partlist, we should utilize map
to speed up the check.
I'm not sure what we should utilize map in this case because chance that
number of merging partitions exceed dozens is low.
Is there a function example that uses a map for such a small number of
elements?3)
For check_parent_values_in_new_partitions():
+ if (!find_value_in_new_partitions(&key->partsupfunc[0], + key->partcollation, parts,nparts, datum, false))
+ found = false;
It seems we can break out of the loop when found is false.
We have implicit "break" in "for" construction:
+ for (i = 0; i < boundinfo->ndatums && found; i++)
I'll change it to explicit "break;" to avoid confusion.
Attached patch with the changes described above.
--
With best regards,
Dmitry KovalPostgres Professional: http://postgrespro.com
Hi,
Thanks for your response.
w.r.t. #2, I think using nested loop is fine for now.
If, when this feature is merged, some user comes up with long merge list,
we can revisit this topic.
Cheers
Hi!
Patch stop applying due to changes in upstream.
Here is a rebased version.
--
With best regards,
Dmitry Koval
Postgres Professional: http://postgrespro.com
Attachments:
v4-0001-partitions-split-merge.patchtext/plain; charset=UTF-8; name=v4-0001-partitions-split-merge.patchDownload+5298-29
On Wed, Jul 13, 2022 at 11:28 AM Dmitry Koval <d.koval@postgrespro.ru>
wrote:
Hi!
Patch stop applying due to changes in upstream.
Here is a rebased version.--
With best regards,
Dmitry KovalPostgres Professional: http://postgrespro.com
Hi,
+attachPartTable(List **wqueue, Relation rel, Relation partition,
PartitionBoundSpec *bound)
I checked naming of existing methods, such as AttachPartitionEnsureIndexes.
I think it would be better if the above method is
named attachPartitionTable.
+ if (!defaultPartCtx && OidIsValid(defaultPartOid))
+ {
+ pc = createSplitPartitionContext(table_open(defaultPartOid,
AccessExclusiveLock));Since the value of pc would be passed to defaultPartCtx, there is no need
to assign to pc above. You can assign directly to defaultPartCtx.
+ /* Drop splitted partition. */
splitted -> split
+ /* Rename new partition if it is need. */
need -> needed.
Cheers
Thanks you!
I've fixed all things mentioned.
--
With best regards,
Dmitry Koval
Postgres Professional: http://postgrespro.com
Attachments:
v5-0001-partitions-split-merge.patchtext/plain; charset=UTF-8; name=v5-0001-partitions-split-merge.patchDownload+5296-29
On Wed, Jul 13, 2022 at 1:05 PM Dmitry Koval <d.koval@postgrespro.ru> wrote:
Thanks you!
I've fixed all things mentioned.--
With best regards,
Dmitry KovalPostgres Professional: http://postgrespro.com
Hi,
Toward the end of ATExecSplitPartition():
+ /* Unlock new partition. */
+ table_close(newPartRel, NoLock);
Why is NoLock passed (instead of AccessExclusiveLock) ?
Cheers
+ /* Unlock new partition. */
+ table_close(newPartRel, NoLock);Why is NoLock passed (instead of AccessExclusiveLock) ?
Thanks!
You're right, I replaced the comment with "Keep the lock until commit.".
--
With best regards,
Dmitry Koval
Postgres Professional: http://postgrespro.com
Attachments:
v6-0001-partitions-split-merge.patchtext/plain; charset=UTF-8; name=v6-0001-partitions-split-merge.patchDownload+5296-29
This is not a review, but I think the isolation tests should be
expanded. At least, include the case of serializable transactions being
involved.
--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
"Pensar que el espectro que vemos es ilusorio no lo despoja de espanto,
sólo le suma el nuevo terror de la locura" (Perelandra, C.S. Lewis)
This is not a review, but I think the isolation tests should be
expanded. At least, include the case of serializable transactions being
involved.
Thanks!
I will expand the tests for the next commitfest.
--
With best regards,
Dmitry Koval
Postgres Professional: http://postgrespro.com
Hi!
Patch stop applying due to changes in upstream.
Here is a rebased version.
--
With best regards,
Dmitry Koval
Postgres Professional: http://postgrespro.com
Attachments:
v7-0001-partitions-split-merge.patchtext/plain; charset=UTF-8; name=v7-0001-partitions-split-merge.patchDownload+5293-29
I will expand the tests for the next commitfest.
Hi!
Combinations of isolation modes (READ COMMITTED/REPEATABLE
READ/SERIALIZABLE) were added to test
src/test/isolation/specs/partition-split-merge.spec
--
With best regards,
Dmitry Koval
Postgres Professional: http://postgrespro.com
Attachments:
v8-0001-partitions-split-merge.patchtext/plain; charset=UTF-8; name=v8-0001-partitions-split-merge.patchDownload+5675-29
Hi!
Patch stop applying due to changes in upstream.
Here is a rebased version.
--
With best regards,
Dmitry Koval
Postgres Professional: http://postgrespro.com
Attachments:
v9-0001-partitions-split-merge.patchtext/plain; charset=UTF-8; name=v9-0001-partitions-split-merge.patchDownload+5675-29
On Wed, Sep 07, 2022 at 08:03:09PM +0300, Dmitry Koval wrote:
Hi!
Patch stop applying due to changes in upstream.
Here is a rebased version.
This crashes on freebsd with -DRELCACHE_FORCE_RELEASE
https://cirrus-ci.com/task/6565371623768064
https://cirrus-ci.com/task/6145355992530944
Note that that's a modified cirrus script from my CI improvements branch
which also does some extra/different things.
--
Justin
