public inbox for [email protected]
help / color / mirror / Atom feedFrom: Alberto Piai <[email protected]>
Subject: [PATCH v1] Fix ALTER COLUMN ... DROP EXPRESSSION with subpartitions
Date: Sun, 5 Apr 2026 06:10:41 +0200
A column can be GENERATED only if it is such in the whole inheritance
tree (see 8bf6ec3ba3a44448817af47a080587f3b71bee08).
For this reason, ATPrepDropExpression refuses to be called with ONLY on
a partitioned table. To detect this, the current implementation checks
whether recurse is set to false and the rel has direct children.
Recursion is implemented with ATSimpleRecursion, which calls ATPrepCmd
with recurse = false for every node in the tree. Inner nodes (for
example a partition which itself has subpartitions) then fail the check,
accidentally preventing the command from working on inheritance trees of
depth > 2.
This fixes it by also checking that we're at the top level of the
recursive calls using the recursing parameter, which is always true when
called through ATSimpleRecursion, always false when invoked on the root
rel.
---
src/backend/commands/tablecmds.c | 6 +++-
.../regress/expected/generated_stored.out | 36 +++++++++++++++++++
src/test/regress/sql/generated_stored.sql | 10 ++++++
3 files changed, 51 insertions(+), 1 deletion(-)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 0ce2e81f9c2..d54b33bf897 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -8839,8 +8839,12 @@ ATPrepDropExpression(Relation rel, AlterTableCmd *cmd, bool recurse, bool recurs
* here, we'd need extra code to update attislocal of the direct child
* tables, somewhat similar to how DROP COLUMN does it, so that the
* resulting state can be properly dumped and restored.
+ *
+ * We must check this only on the root node of an inheritance tree
+ * (recursing = false), otherwise it would be impossible to apply this
+ * operation recursively to trees with depth > 2.
*/
- if (!recurse &&
+ if (!recursing && !recurse &&
find_inheritance_children(RelationGetRelid(rel), lockmode))
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
diff --git a/src/test/regress/expected/generated_stored.out b/src/test/regress/expected/generated_stored.out
index 43cddeac373..49ae13f9c14 100644
--- a/src/test/regress/expected/generated_stored.out
+++ b/src/test/regress/expected/generated_stored.out
@@ -1134,6 +1134,42 @@ SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
(3 rows)
-- we leave these tables around for purposes of testing dump/reload/upgrade
+-- test drop expression with subpartitions
+CREATE TABLE gtest_root (a int, b int, c int GENERATED ALWAYS AS (a + b) STORED) PARTITION BY LIST (a);
+CREATE TABLE gtest_node PARTITION OF gtest_root FOR VALUES IN (1) PARTITION BY LIST (b);
+CREATE TABLE gtest_leaf PARTITION OF gtest_node FOR VALUES IN (1);
+ALTER TABLE gtest_root ALTER COLUMN c DROP EXPRESSION;
+\d gtest_root
+Partitioned table "generated_stored_tests.gtest_root"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+ b | integer | | |
+ c | integer | | |
+Partition key: LIST (a)
+Number of partitions: 1 (Use \d+ to list them.)
+
+\d gtest_node
+Partitioned table "generated_stored_tests.gtest_node"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+ b | integer | | |
+ c | integer | | |
+Partition of: gtest_root FOR VALUES IN (1)
+Partition key: LIST (b)
+Number of partitions: 1 (Use \d+ to list them.)
+
+\d gtest_leaf
+ Table "generated_stored_tests.gtest_leaf"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+ b | integer | | |
+ c | integer | | |
+Partition of: gtest_node FOR VALUES IN (1)
+
+DROP TABLE gtest_root;
-- generated columns in partition key (not allowed)
CREATE TABLE gtest_part_key (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE (f3);
ERROR: cannot use generated column in partition key
diff --git a/src/test/regress/sql/generated_stored.sql b/src/test/regress/sql/generated_stored.sql
index 280021d79b7..ef44666b968 100644
--- a/src/test/regress/sql/generated_stored.sql
+++ b/src/test/regress/sql/generated_stored.sql
@@ -541,6 +541,16 @@ ALTER TABLE gtest_parent ALTER COLUMN f3 SET EXPRESSION AS (f2 * 2);
SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
-- we leave these tables around for purposes of testing dump/reload/upgrade
+-- test drop expression with subpartitions
+CREATE TABLE gtest_root (a int, b int, c int GENERATED ALWAYS AS (a + b) STORED) PARTITION BY LIST (a);
+CREATE TABLE gtest_node PARTITION OF gtest_root FOR VALUES IN (1) PARTITION BY LIST (b);
+CREATE TABLE gtest_leaf PARTITION OF gtest_node FOR VALUES IN (1);
+ALTER TABLE gtest_root ALTER COLUMN c DROP EXPRESSION;
+\d gtest_root
+\d gtest_node
+\d gtest_leaf
+DROP TABLE gtest_root;
+
-- generated columns in partition key (not allowed)
CREATE TABLE gtest_part_key (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE (f3);
CREATE TABLE gtest_part_key (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE ((f3));
base-commit: c06443063f01b0996a16dea77462ac6b31eb181d
--
2.47.0
--vd5olc53tjms46zu--
view thread (2+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: [PATCH v1] Fix ALTER COLUMN ... DROP EXPRESSSION with subpartitions
In-Reply-To: <no-message-id-601963@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox