hi. --this ALTER COLUMN DROP EXPRESSION work as expected DROP TABLE IF EXISTS parent cascade; CREATE TABLE parent (a int, d INT GENERATED ALWAYS AS (11) STORED); CREATE TABLE child () INHERITS (parent); ALTER TABLE parent ALTER COLUMN d DROP EXPRESSION;
----- the below (ALTER COLUMN DROP EXPRESSION) should also work. ----- DROP TABLE IF EXISTS parent cascade; CREATE TABLE parent (a int, d INT GENERATED ALWAYS AS (11) STORED); CREATE TABLE child () INHERITS (parent); CREATE TABLE grandchild () INHERITS (child); ALTER TABLE parent ALTER COLUMN d DROP EXPRESSION; but currently it will generated error: ERROR: 0A000: ALTER TABLE / DROP EXPRESSION must be applied to child tables too LOCATION: ATPrepDropExpression, tablecmds.c:8734 The attached patch fixes this potential issue.
From 78f2d0734d492296289671cc0b740329d1f2da30 Mon Sep 17 00:00:00 2001 From: jian he <[email protected]> Date: Sun, 24 Aug 2025 16:57:01 +0800 Subject: [PATCH v1 1/1] Fix ALTER TABLE DROP EXPRESSION with inheritance hierarchy --this works. DROP TABLE IF EXISTS parent cascade; CREATE TABLE parent (a int, d INT GENERATED ALWAYS AS (11) STORED); CREATE TABLE child () INHERITS (parent); ALTER TABLE parent ALTER COLUMN d DROP EXPRESSION; -- so the below should also works. DROP TABLE IF EXISTS parent cascade; CREATE TABLE parent (a int, d INT GENERATED ALWAYS AS (11) STORED); CREATE TABLE child () INHERITS (parent); CREATE TABLE grandchild () INHERITS (child); ALTER TABLE parent ALTER COLUMN d DROP EXPRESSION; discussion: https://postgr.es/m/ --- src/backend/commands/tablecmds.c | 11 +++++++---- src/test/regress/expected/generated_stored.out | 14 +++++++++++++- src/test/regress/expected/generated_virtual.out | 14 +++++++++++++- src/test/regress/sql/generated_stored.sql | 2 ++ src/test/regress/sql/generated_virtual.sql | 2 ++ 5 files changed, 37 insertions(+), 6 deletions(-) diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 082a3575d62..cbc5ab7d7c0 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -521,7 +521,8 @@ static ObjectAddress ATExecDropIdentity(Relation rel, const char *colName, bool bool recurse, bool recursing); static ObjectAddress ATExecSetExpression(AlteredTableInfo *tab, Relation rel, const char *colName, Node *newExpr, LOCKMODE lockmode); -static void ATPrepDropExpression(Relation rel, AlterTableCmd *cmd, bool recurse, bool recursing, LOCKMODE lockmode); +static void ATPrepDropExpression(Relation rel, AlterTableCmd *cmd, bool recurse, bool recursing, LOCKMODE lockmode, + AlterTableUtilityContext *context); static ObjectAddress ATExecDropExpression(Relation rel, const char *colName, bool missing_ok, LOCKMODE lockmode); static ObjectAddress ATExecSetStatistics(Relation rel, const char *colName, int16 colNum, Node *newValue, LOCKMODE lockmode); @@ -5024,7 +5025,7 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd, ATSimplePermissions(cmd->subtype, rel, ATT_TABLE | ATT_PARTITIONED_TABLE | ATT_FOREIGN_TABLE); ATSimpleRecursion(wqueue, rel, cmd, recurse, lockmode, context); - ATPrepDropExpression(rel, cmd, recurse, recursing, lockmode); + ATPrepDropExpression(rel, cmd, recurse, recursing, lockmode, context); pass = AT_PASS_DROP; break; case AT_SetStatistics: /* ALTER COLUMN SET STATISTICS */ @@ -8717,7 +8718,8 @@ ATExecSetExpression(AlteredTableInfo *tab, Relation rel, const char *colName, * ALTER TABLE ALTER COLUMN DROP EXPRESSION */ static void -ATPrepDropExpression(Relation rel, AlterTableCmd *cmd, bool recurse, bool recursing, LOCKMODE lockmode) +ATPrepDropExpression(Relation rel, AlterTableCmd *cmd, bool recurse, bool recursing, LOCKMODE lockmode, + AlterTableUtilityContext *context) { /* * Reject ONLY if there are child tables. We could implement this, but it @@ -8730,7 +8732,8 @@ ATPrepDropExpression(Relation rel, AlterTableCmd *cmd, bool recurse, bool recurs * resulting state can be properly dumped and restored. */ if (!recurse && - find_inheritance_children(RelationGetRelid(rel), lockmode)) + find_inheritance_children(RelationGetRelid(rel), lockmode) && + RelationGetRelid(rel) == context->relid) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("ALTER TABLE / DROP EXPRESSION must be applied to child tables too"))); diff --git a/src/test/regress/expected/generated_stored.out b/src/test/regress/expected/generated_stored.out index adac2cedfb2..a2b04f564eb 100644 --- a/src/test/regress/expected/generated_stored.out +++ b/src/test/regress/expected/generated_stored.out @@ -1264,6 +1264,7 @@ CREATE TABLE gtest30 ( b int GENERATED ALWAYS AS (a * 2) STORED ); CREATE TABLE gtest30_1 () INHERITS (gtest30); +CREATE TABLE gtest30_1_1 () INHERITS (gtest30_1); ALTER TABLE gtest30 ALTER COLUMN b DROP EXPRESSION; \d gtest30 Table "generated_stored_tests.gtest30" @@ -1280,9 +1281,20 @@ Number of child tables: 1 (Use \d+ to list them.) a | integer | | | b | integer | | | Inherits: gtest30 +Number of child tables: 1 (Use \d+ to list them.) + +\d gtest30_1_1 + Table "generated_stored_tests.gtest30_1_1" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + a | integer | | | + b | integer | | | +Inherits: gtest30_1 DROP TABLE gtest30 CASCADE; -NOTICE: drop cascades to table gtest30_1 +NOTICE: drop cascades to 2 other objects +DETAIL: drop cascades to table gtest30_1 +drop cascades to table gtest30_1_1 CREATE TABLE gtest30 ( a int, b int GENERATED ALWAYS AS (a * 2) STORED diff --git a/src/test/regress/expected/generated_virtual.out b/src/test/regress/expected/generated_virtual.out index aca6347babe..50a32996f4f 100644 --- a/src/test/regress/expected/generated_virtual.out +++ b/src/test/regress/expected/generated_virtual.out @@ -1232,6 +1232,7 @@ CREATE TABLE gtest30 ( b int GENERATED ALWAYS AS (a * 2) VIRTUAL ); CREATE TABLE gtest30_1 () INHERITS (gtest30); +CREATE TABLE gtest30_1_1 () INHERITS (gtest30_1); ALTER TABLE gtest30 ALTER COLUMN b DROP EXPRESSION; ERROR: ALTER TABLE / DROP EXPRESSION is not supported for virtual generated columns DETAIL: Column "b" of relation "gtest30" is a virtual generated column. @@ -1250,9 +1251,20 @@ Number of child tables: 1 (Use \d+ to list them.) a | integer | | | b | integer | | | generated always as (a * 2) Inherits: gtest30 +Number of child tables: 1 (Use \d+ to list them.) + +\d gtest30_1_1 + Table "generated_virtual_tests.gtest30_1_1" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+----------------------------- + a | integer | | | + b | integer | | | generated always as (a * 2) +Inherits: gtest30_1 DROP TABLE gtest30 CASCADE; -NOTICE: drop cascades to table gtest30_1 +NOTICE: drop cascades to 2 other objects +DETAIL: drop cascades to table gtest30_1 +drop cascades to table gtest30_1_1 CREATE TABLE gtest30 ( a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL diff --git a/src/test/regress/sql/generated_stored.sql b/src/test/regress/sql/generated_stored.sql index f56fde8d4e5..3c8931c677e 100644 --- a/src/test/regress/sql/generated_stored.sql +++ b/src/test/regress/sql/generated_stored.sql @@ -577,9 +577,11 @@ CREATE TABLE gtest30 ( b int GENERATED ALWAYS AS (a * 2) STORED ); CREATE TABLE gtest30_1 () INHERITS (gtest30); +CREATE TABLE gtest30_1_1 () INHERITS (gtest30_1); ALTER TABLE gtest30 ALTER COLUMN b DROP EXPRESSION; \d gtest30 \d gtest30_1 +\d gtest30_1_1 DROP TABLE gtest30 CASCADE; CREATE TABLE gtest30 ( a int, diff --git a/src/test/regress/sql/generated_virtual.sql b/src/test/regress/sql/generated_virtual.sql index ba19bc4c701..5696be4b36a 100644 --- a/src/test/regress/sql/generated_virtual.sql +++ b/src/test/regress/sql/generated_virtual.sql @@ -628,9 +628,11 @@ CREATE TABLE gtest30 ( b int GENERATED ALWAYS AS (a * 2) VIRTUAL ); CREATE TABLE gtest30_1 () INHERITS (gtest30); +CREATE TABLE gtest30_1_1 () INHERITS (gtest30_1); ALTER TABLE gtest30 ALTER COLUMN b DROP EXPRESSION; \d gtest30 \d gtest30_1 +\d gtest30_1_1 DROP TABLE gtest30 CASCADE; CREATE TABLE gtest30 ( a int, -- 2.34.1
