While working on [0], I noticed that DROP EXPRESSION currently refuses
to be applied to inheritance trees of depth > 2, e.g. when there are
subpartitions.
This works as expected:
CREATE TABLE gtest_root
(a int, b int, c int GENERATED ALWAYS AS (a + b) STORED)
PARTITION BY LIST (a);
CREATE TABLE gtest_leaf
PARTITION OF gtest_root FOR VALUES IN (1);
ALTER TABLE gtest_root ALTER COLUMN c DROP EXPRESSION;
while this doesn't:
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;
and results in
ERROR: ALTER TABLE / DROP EXPRESSION must be applied to child tables too
This seems like a simple oversight while trying to enforce that a
GENERATED column must be such in the whole inheritance tree [1].
PFA a fix for this and a test case.
I added the test case to generated_stored.sql, even though the comments
at the top say it should be kept in sync with generated_virtual.sql,
because DROP EXPRESSION is not supported for virtual generated columns.
It seemed better to keep the test case closed to the other tests of
DROP/SET EXPRESSION with partitioning, rather than putting it e.g. in
alter_table.sql, but happy to move it of course.
Kind regards,
Alberto
[0] https://postgr.es/m/abkrpUwlGngF4e-d%40phidippus.sen.work
[1] See 8bf6ec3ba3a44448817af47a080587f3b71bee08 and the associated
discussion at https://postgr.es/m/[email protected]
--
Alberto Piai
Sensational AG
Zürich, Switzerland
>From 73318f99618b281534ce89322371e22d81771f05 Mon Sep 17 00:00:00 2001
From: Alberto Piai <[email protected]>
Date: Sun, 5 Apr 2026 06:10:41 +0200
Subject: [PATCH v1] Fix ALTER COLUMN ... DROP EXPRESSSION with subpartitions
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
