On Sat, Dec 20, 2025 at 6:42 PM Alexander Korotkov <[email protected]> wrote: > > Hi Kirill!
> > reshke=# alter table only z merge partitions (z_1,z_2) into z_12; > > ALTER TABLE > > reshke=# > > > > ``` > > > > IMO "alter table only ... merge partitions" does not make perfect > > sense and should be rejected rather than executed. WDYT? > > Could you, please, clarify your point? I didn't quite get it. It > looks like pretty basic example of merging two adjacent partitions. > > reshke=# alter table only z merge partitions (z_1,z_2) into z_12; I think it should fail, because we are not applying to table "z" itself, For Split/Merge partitions, we are processing the whole partitioned table z hierarchy. alter table z merge partitions (z_1,z_2) into z_12; should work. I guess the attached maybe is what Krill wants. -- jian https://www.enterprisedb.com
From 4ddd54a1516b8e72aaede2b9722f457967ac7f0c Mon Sep 17 00:00:00 2001 From: jian he <[email protected]> Date: Sat, 20 Dec 2025 19:03:47 +0800 Subject: [PATCH v1 1/1] disallow ALTER TABLE ONLY in MERGE SPLIT PARTITIONS dicussion: : CALdSSPj6ixZC2EaTgJU_2BNhgjsg_8665x2-pDzcEoc-PRueAA@mail.gmail.com">https://postgr.es/m/CALdSSPj6ixZC2EaTgJU_2BNhgjsg_8665x2-pDzcEoc-PRueAA@mail.gmail.com --- src/backend/commands/tablecmds.c | 7 +++++-- src/backend/parser/parse_utilcmd.c | 12 ++++++++++++ src/test/regress/expected/partition_merge.out | 4 ++++ src/test/regress/expected/partition_split.out | 6 ++++++ src/test/regress/sql/partition_merge.sql | 4 ++++ src/test/regress/sql/partition_split.sql | 5 +++++ 6 files changed, 36 insertions(+), 2 deletions(-) diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 6b1a00ed477..e3da673c417 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -5283,6 +5283,9 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd, break; case AT_MergePartitions: case AT_SplitPartition: + /* Set up recursion for phase 2; no other prep needed */ + if (recurse) + cmd->recurse = true; ATSimplePermissions(cmd->subtype, rel, ATT_PARTITIONED_TABLE); /* No command-specific prep needed */ pass = AT_PASS_MISC; @@ -5684,7 +5687,7 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, address = ATExecDetachPartitionFinalize(rel, ((PartitionCmd *) cmd->def)->name); break; case AT_MergePartitions: - cmd = ATParseTransformCmd(wqueue, tab, rel, cmd, false, lockmode, + cmd = ATParseTransformCmd(wqueue, tab, rel, cmd, cmd->recurse, lockmode, cur_pass, context); Assert(cmd != NULL); Assert(rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE); @@ -5692,7 +5695,7 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, context); break; case AT_SplitPartition: - cmd = ATParseTransformCmd(wqueue, tab, rel, cmd, false, lockmode, + cmd = ATParseTransformCmd(wqueue, tab, rel, cmd, cmd->recurse, lockmode, cur_pass, context); Assert(cmd != NULL); Assert(rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE); diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c index 2b7b084f216..2d0d28d6ddb 100644 --- a/src/backend/parser/parse_utilcmd.c +++ b/src/backend/parser/parse_utilcmd.c @@ -4080,6 +4080,12 @@ transformAlterTableStmt(Oid relid, AlterTableStmt *stmt, errcode(ERRCODE_INVALID_OBJECT_DEFINITION), errmsg("list of partitions to be merged should include at least two partitions")); + if (!cxt.relation->inh) + ereport(ERROR, + errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("%s must apply to child tables too", "ALTER TABLE MERGE PARTITIONS"), + errhint("Do not specify the ONLY keyword.")); + transformPartitionCmdForMerge(&cxt, partcmd); newcmds = lappend(newcmds, cmd); break; @@ -4094,6 +4100,12 @@ transformAlterTableStmt(Oid relid, AlterTableStmt *stmt, errcode(ERRCODE_INVALID_OBJECT_DEFINITION), errmsg("list of new partitions should contain at least two partitions")); + if (!cxt.relation->inh) + ereport(ERROR, + errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("%s must apply to child tables too", "ALTER TABLE SPLIT PARTITIONS"), + errhint("Do not specify the ONLY keyword.")); + transformPartitionCmdForSplit(&cxt, partcmd); newcmds = lappend(newcmds, cmd); break; diff --git a/src/test/regress/expected/partition_merge.out b/src/test/regress/expected/partition_merge.out index 3e40abf38a0..e5b30adeb30 100644 --- a/src/test/regress/expected/partition_merge.out +++ b/src/test/regress/expected/partition_merge.out @@ -21,6 +21,10 @@ CREATE TABLE sales_apr_1 PARTITION OF sales_apr2022 FOR VALUES FROM ('2022-04-01 CREATE TABLE sales_apr_2 PARTITION OF sales_apr2022 FOR VALUES FROM ('2022-04-15') TO ('2022-05-01'); ALTER TABLE sales_range ATTACH PARTITION sales_apr2022 FOR VALUES FROM ('2022-04-01') TO ('2022-05-01'); CREATE TABLE sales_others PARTITION OF sales_range DEFAULT; +-- ERROR, can not specify ONLY +ALTER TABLE ONLY sales_range MERGE PARTITIONS (sales_feb2022, sales_mar2022) INTO sales_feb_mar_apr2022; +ERROR: ALTER TABLE MERGE PARTITIONS must apply to child tables too +HINT: Do not specify the ONLY keyword. -- ERROR: partition with name "sales_feb2022" is already used ALTER TABLE sales_range MERGE PARTITIONS (sales_feb2022, sales_mar2022, sales_feb2022) INTO sales_feb_mar_apr2022; ERROR: partition with name "sales_feb2022" is already used diff --git a/src/test/regress/expected/partition_split.out b/src/test/regress/expected/partition_split.out index e68baf71daf..766ab7c988a 100644 --- a/src/test/regress/expected/partition_split.out +++ b/src/test/regress/expected/partition_split.out @@ -15,6 +15,12 @@ CREATE TABLE sales_range (salesperson_id int, sales_date date) PARTITION BY RANG 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; +-- ERROR, can not specify ONLY +ALTER TABLE ONLY sales_range SPLIT PARTITION sales_jan2022 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')); +ERROR: ALTER TABLE SPLIT PARTITIONS must apply to child tables too +HINT: Do not specify the ONLY keyword. -- ERROR: relation "sales_xxx" does not exist ALTER TABLE sales_range SPLIT PARTITION sales_xxx INTO (PARTITION sales_feb2022 FOR VALUES FROM ('2022-02-01') TO ('2022-03-01'), diff --git a/src/test/regress/sql/partition_merge.sql b/src/test/regress/sql/partition_merge.sql index ffb498612a6..c219130a0d8 100644 --- a/src/test/regress/sql/partition_merge.sql +++ b/src/test/regress/sql/partition_merge.sql @@ -27,8 +27,12 @@ ALTER TABLE sales_range ATTACH PARTITION sales_apr2022 FOR VALUES FROM ('2022-04 CREATE TABLE sales_others PARTITION OF sales_range DEFAULT; +-- ERROR, can not specify ONLY +ALTER TABLE ONLY sales_range MERGE PARTITIONS (sales_feb2022, sales_mar2022) INTO sales_feb_mar_apr2022; + -- ERROR: partition with name "sales_feb2022" is already used ALTER TABLE sales_range MERGE PARTITIONS (sales_feb2022, sales_mar2022, sales_feb2022) INTO sales_feb_mar_apr2022; + -- ERROR: "sales_apr2022" is not a table ALTER TABLE sales_range MERGE PARTITIONS (sales_feb2022, sales_mar2022, sales_apr2022) INTO sales_feb_mar_apr2022; -- ERROR: can not merge partition "sales_mar2022" together with partition "sales_jan2022" diff --git a/src/test/regress/sql/partition_split.sql b/src/test/regress/sql/partition_split.sql index 0e79c036ea9..36714a3a892 100644 --- a/src/test/regress/sql/partition_split.sql +++ b/src/test/regress/sql/partition_split.sql @@ -19,6 +19,11 @@ CREATE TABLE sales_jan2022 PARTITION OF sales_range FOR VALUES FROM ('2022-01-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; +-- ERROR, can not specify ONLY +ALTER TABLE ONLY sales_range SPLIT PARTITION sales_jan2022 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')); + -- ERROR: relation "sales_xxx" does not exist ALTER TABLE sales_range SPLIT PARTITION sales_xxx INTO (PARTITION sales_feb2022 FOR VALUES FROM ('2022-02-01') TO ('2022-03-01'), -- 2.39.5 (Apple Git-154)
From 5f52cc7cdad88f5d0e6677548e27e4354ef3c532 Mon Sep 17 00:00:00 2001 From: jian he <[email protected]> Date: Sat, 20 Dec 2025 19:12:58 +0800 Subject: [PATCH v1 1/1] disallow ALTER TABLE ONLY in MERGE SPLIT PARTITIONS dicussion: : CALdSSPj6ixZC2EaTgJU_2BNhgjsg_8665x2-pDzcEoc-PRueAA@mail.gmail.com">https://postgr.es/m/CALdSSPj6ixZC2EaTgJU_2BNhgjsg_8665x2-pDzcEoc-PRueAA@mail.gmail.com --- src/backend/commands/tablecmds.c | 7 +++++-- src/backend/parser/parse_utilcmd.c | 12 ++++++++++++ src/test/regress/expected/partition_merge.out | 4 ++++ src/test/regress/expected/partition_split.out | 6 ++++++ src/test/regress/sql/partition_merge.sql | 3 +++ src/test/regress/sql/partition_split.sql | 5 +++++ 6 files changed, 35 insertions(+), 2 deletions(-) diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 6b1a00ed477..e3da673c417 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -5283,6 +5283,9 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd, break; case AT_MergePartitions: case AT_SplitPartition: + /* Set up recursion for phase 2; no other prep needed */ + if (recurse) + cmd->recurse = true; ATSimplePermissions(cmd->subtype, rel, ATT_PARTITIONED_TABLE); /* No command-specific prep needed */ pass = AT_PASS_MISC; @@ -5684,7 +5687,7 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, address = ATExecDetachPartitionFinalize(rel, ((PartitionCmd *) cmd->def)->name); break; case AT_MergePartitions: - cmd = ATParseTransformCmd(wqueue, tab, rel, cmd, false, lockmode, + cmd = ATParseTransformCmd(wqueue, tab, rel, cmd, cmd->recurse, lockmode, cur_pass, context); Assert(cmd != NULL); Assert(rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE); @@ -5692,7 +5695,7 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, context); break; case AT_SplitPartition: - cmd = ATParseTransformCmd(wqueue, tab, rel, cmd, false, lockmode, + cmd = ATParseTransformCmd(wqueue, tab, rel, cmd, cmd->recurse, lockmode, cur_pass, context); Assert(cmd != NULL); Assert(rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE); diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c index 2b7b084f216..2d0d28d6ddb 100644 --- a/src/backend/parser/parse_utilcmd.c +++ b/src/backend/parser/parse_utilcmd.c @@ -4080,6 +4080,12 @@ transformAlterTableStmt(Oid relid, AlterTableStmt *stmt, errcode(ERRCODE_INVALID_OBJECT_DEFINITION), errmsg("list of partitions to be merged should include at least two partitions")); + if (!cxt.relation->inh) + ereport(ERROR, + errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("%s must apply to child tables too", "ALTER TABLE MERGE PARTITIONS"), + errhint("Do not specify the ONLY keyword.")); + transformPartitionCmdForMerge(&cxt, partcmd); newcmds = lappend(newcmds, cmd); break; @@ -4094,6 +4100,12 @@ transformAlterTableStmt(Oid relid, AlterTableStmt *stmt, errcode(ERRCODE_INVALID_OBJECT_DEFINITION), errmsg("list of new partitions should contain at least two partitions")); + if (!cxt.relation->inh) + ereport(ERROR, + errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("%s must apply to child tables too", "ALTER TABLE SPLIT PARTITIONS"), + errhint("Do not specify the ONLY keyword.")); + transformPartitionCmdForSplit(&cxt, partcmd); newcmds = lappend(newcmds, cmd); break; diff --git a/src/test/regress/expected/partition_merge.out b/src/test/regress/expected/partition_merge.out index 3e40abf38a0..e5b30adeb30 100644 --- a/src/test/regress/expected/partition_merge.out +++ b/src/test/regress/expected/partition_merge.out @@ -21,6 +21,10 @@ CREATE TABLE sales_apr_1 PARTITION OF sales_apr2022 FOR VALUES FROM ('2022-04-01 CREATE TABLE sales_apr_2 PARTITION OF sales_apr2022 FOR VALUES FROM ('2022-04-15') TO ('2022-05-01'); ALTER TABLE sales_range ATTACH PARTITION sales_apr2022 FOR VALUES FROM ('2022-04-01') TO ('2022-05-01'); CREATE TABLE sales_others PARTITION OF sales_range DEFAULT; +-- ERROR, can not specify ONLY +ALTER TABLE ONLY sales_range MERGE PARTITIONS (sales_feb2022, sales_mar2022) INTO sales_feb_mar_apr2022; +ERROR: ALTER TABLE MERGE PARTITIONS must apply to child tables too +HINT: Do not specify the ONLY keyword. -- ERROR: partition with name "sales_feb2022" is already used ALTER TABLE sales_range MERGE PARTITIONS (sales_feb2022, sales_mar2022, sales_feb2022) INTO sales_feb_mar_apr2022; ERROR: partition with name "sales_feb2022" is already used diff --git a/src/test/regress/expected/partition_split.out b/src/test/regress/expected/partition_split.out index e68baf71daf..766ab7c988a 100644 --- a/src/test/regress/expected/partition_split.out +++ b/src/test/regress/expected/partition_split.out @@ -15,6 +15,12 @@ CREATE TABLE sales_range (salesperson_id int, sales_date date) PARTITION BY RANG 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; +-- ERROR, can not specify ONLY +ALTER TABLE ONLY sales_range SPLIT PARTITION sales_jan2022 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')); +ERROR: ALTER TABLE SPLIT PARTITIONS must apply to child tables too +HINT: Do not specify the ONLY keyword. -- ERROR: relation "sales_xxx" does not exist ALTER TABLE sales_range SPLIT PARTITION sales_xxx INTO (PARTITION sales_feb2022 FOR VALUES FROM ('2022-02-01') TO ('2022-03-01'), diff --git a/src/test/regress/sql/partition_merge.sql b/src/test/regress/sql/partition_merge.sql index ffb498612a6..a51ae1b090f 100644 --- a/src/test/regress/sql/partition_merge.sql +++ b/src/test/regress/sql/partition_merge.sql @@ -27,6 +27,9 @@ ALTER TABLE sales_range ATTACH PARTITION sales_apr2022 FOR VALUES FROM ('2022-04 CREATE TABLE sales_others PARTITION OF sales_range DEFAULT; +-- ERROR, can not specify ONLY +ALTER TABLE ONLY sales_range MERGE PARTITIONS (sales_feb2022, sales_mar2022) INTO sales_feb_mar_apr2022; + -- ERROR: partition with name "sales_feb2022" is already used ALTER TABLE sales_range MERGE PARTITIONS (sales_feb2022, sales_mar2022, sales_feb2022) INTO sales_feb_mar_apr2022; -- ERROR: "sales_apr2022" is not a table diff --git a/src/test/regress/sql/partition_split.sql b/src/test/regress/sql/partition_split.sql index 0e79c036ea9..36714a3a892 100644 --- a/src/test/regress/sql/partition_split.sql +++ b/src/test/regress/sql/partition_split.sql @@ -19,6 +19,11 @@ CREATE TABLE sales_jan2022 PARTITION OF sales_range FOR VALUES FROM ('2022-01-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; +-- ERROR, can not specify ONLY +ALTER TABLE ONLY sales_range SPLIT PARTITION sales_jan2022 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')); + -- ERROR: relation "sales_xxx" does not exist ALTER TABLE sales_range SPLIT PARTITION sales_xxx INTO (PARTITION sales_feb2022 FOR VALUES FROM ('2022-02-01') TO ('2022-03-01'), -- 2.39.5 (Apple Git-154)
