v1-0001-Fix-ALTER-TABLE-DROP-EXPRESSION-with-inheritance-hierarch.patch
text/x-patch
Filename: v1-0001-Fix-ALTER-TABLE-DROP-EXPRESSION-with-inheritance-hierarch.patch
Type: text/x-patch
Part: 0
Patch
Same data as JSON:
GET /api/v1/attachments/:id/patch
the parsed metadata as JSON — format, series position, per-file stats; never the diff bytes.
API reference →
Format: format-patch
Series: patch v1-0001
Subject: Fix ALTER TABLE DROP EXPRESSION with inheritance hierarchy
| File | + | − |
|---|---|---|
| src/backend/commands/tablecmds.c | 7 | 4 |
| src/test/regress/expected/generated_stored.out | 13 | 1 |
| src/test/regress/expected/generated_virtual.out | 13 | 1 |
| src/test/regress/sql/generated_stored.sql | 2 | 0 |
| src/test/regress/sql/generated_virtual.sql | 2 | 0 |
From 78f2d0734d492296289671cc0b740329d1f2da30 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
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