v3-0001-Fix-ALTER-TABLE-DROP-EXPRESSION-with-inheritance-.patch
application/octet-stream
Filename: v3-0001-Fix-ALTER-TABLE-DROP-EXPRESSION-with-inheritance-.patch
Type: application/octet-stream
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 v3-0001
Subject: Fix ALTER TABLE DROP EXPRESSION with inheritance hierarchy
| File | + | − |
|---|---|---|
| src/backend/commands/tablecmds.c | 4 | 3 |
| src/test/regress/expected/generated_stored.out | 14 | 1 |
| src/test/regress/expected/generated_virtual.out | 14 | 1 |
| src/test/regress/sql/generated_stored.sql | 2 | 0 |
| src/test/regress/sql/generated_virtual.sql | 2 | 0 |
From b70c90b86d5bf810ab2d57c99fa337c14e85bc83 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 v3] 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 | 7 ++++---
src/test/regress/expected/generated_stored.out | 15 ++++++++++++++-
src/test/regress/expected/generated_virtual.out | 15 ++++++++++++++-
src/test/regress/sql/generated_stored.sql | 2 ++
src/test/regress/sql/generated_virtual.sql | 2 ++
5 files changed, 36 insertions(+), 5 deletions(-)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 082a3575d62..b6b48fe1b57 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -8729,11 +8729,12 @@ ATPrepDropExpression(Relation rel, AlterTableCmd *cmd, bool recurse, bool recurs
* tables, somewhat similar to how DROP COLUMN does it, so that the
* resulting state can be properly dumped and restored.
*/
- if (!recurse &&
+ if (!recurse && !recursing &&
find_inheritance_children(RelationGetRelid(rel), lockmode))
ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("ALTER TABLE / DROP EXPRESSION must be applied to child tables too")));
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("ALTER TABLE / DROP EXPRESSION must be applied to child tables too"),
+ errhint("Do not specify the ONLY keyword."));
/*
* Cannot drop generation expression from inherited columns.
diff --git a/src/test/regress/expected/generated_stored.out b/src/test/regress/expected/generated_stored.out
index adac2cedfb2..36af4e1d700 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
@@ -1290,6 +1302,7 @@ CREATE TABLE gtest30 (
CREATE TABLE gtest30_1 () INHERITS (gtest30);
ALTER TABLE ONLY gtest30 ALTER COLUMN b DROP EXPRESSION; -- error
ERROR: ALTER TABLE / DROP EXPRESSION must be applied to child tables too
+HINT: Do not specify the ONLY keyword.
\d gtest30
Table "generated_stored_tests.gtest30"
Column | Type | Collation | Nullable | Default
diff --git a/src/test/regress/expected/generated_virtual.out b/src/test/regress/expected/generated_virtual.out
index aca6347babe..565a6357f39 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
@@ -1260,6 +1272,7 @@ CREATE TABLE gtest30 (
CREATE TABLE gtest30_1 () INHERITS (gtest30);
ALTER TABLE ONLY gtest30 ALTER COLUMN b DROP EXPRESSION; -- error
ERROR: ALTER TABLE / DROP EXPRESSION must be applied to child tables too
+HINT: Do not specify the ONLY keyword.
\d gtest30
Table "generated_virtual_tests.gtest30"
Column | Type | Collation | Nullable | Default
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.43.0