public inbox for [email protected]
help / color / mirror / Atom feedFrom: jian he <[email protected]>
To: PostgreSQL Hackers <[email protected]>
Subject: Re: support ALTER COLUMN SET EXPRESSION over virtual generated column with check constraint
Date: Mon, 7 Jul 2025 10:24:13 +0800
Message-ID: <CACJufxExe7+Gh5MKMFiN5xwwmPXaaZC1jjQOFuovv8Q7b1LgFw@mail.gmail.com> (raw)
In-Reply-To: <CACJufxF9vVYcx53yJpC6Oo7Y7_=20=pmv_t-6SBKAJ63bQXhnQ@mail.gmail.com>
References: <CACJufxH3VETr7orF5rW29GnDk3n1wWbOE3WdkHYd3iPGrQ9E_A@mail.gmail.com>
<CACJufxF9vVYcx53yJpC6Oo7Y7_=20=pmv_t-6SBKAJ63bQXhnQ@mail.gmail.com>
hi.
rebased only.
Attachments:
[text/x-patch] v2-0001-allow-change-generated-expression-while-check-constraint-.patch (8.0K, ../CACJufxExe7+Gh5MKMFiN5xwwmPXaaZC1jjQOFuovv8Q7b1LgFw@mail.gmail.com/2-v2-0001-allow-change-generated-expression-while-check-constraint-.patch)
download | inline diff:
From 3ff7dd0fd260a6b3a35f8652f0840ab96bc68c70 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Mon, 7 Jul 2025 10:07:25 +0800
Subject: [PATCH v2 1/1] allow change generated expression while check
constraint on it
currently, if we have check constraints over virtual generated column
then we can not change the generation expression.
for example:
CREATE TABLE gtest20 (a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL CHECK (b < 50));
INSERT INTO gtest20 (a) VALUES (10);
ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS (a * 3); --error
attached patch try to resolve this issue.
main gotcha is in ATExecSetExpression, RememberAllDependentForRebuilding will do
all the work. also add a test for ALTER TABLE SET EXPRESSION for virtual
generated column will not do table rewrite.
discussion: https://postgr.es/m/CACJufxH3VETr7orF5rW29GnDk3n1wWbOE3WdkHYd3iPGrQ9E_A@mail.gmail.com
commitfest: https://commitfest.postgresql.org/patch/5645/
---
src/backend/commands/tablecmds.c | 26 +++++-----------
.../regress/expected/generated_virtual.out | 31 +++++++++++++++----
src/test/regress/sql/generated_virtual.sql | 18 +++++++++--
3 files changed, 48 insertions(+), 27 deletions(-)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index cb811520c29..d2e91281940 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -8600,18 +8600,6 @@ ATExecSetExpression(AlteredTableInfo *tab, Relation rel, const char *colName,
errmsg("column \"%s\" of relation \"%s\" is not a generated column",
colName, RelationGetRelationName(rel))));
- /*
- * TODO: This could be done, just need to recheck any constraints
- * afterwards.
- */
- if (attgenerated == ATTRIBUTE_GENERATED_VIRTUAL &&
- rel->rd_att->constr && rel->rd_att->constr->num_check > 0)
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("ALTER TABLE / SET EXPRESSION is not supported for virtual generated columns in tables with check constraints"),
- errdetail("Column \"%s\" of relation \"%s\" is a virtual generated column.",
- colName, RelationGetRelationName(rel))));
-
if (attgenerated == ATTRIBUTE_GENERATED_VIRTUAL && attTup->attnotnull)
tab->verify_new_notnull = true;
@@ -8644,15 +8632,15 @@ ATExecSetExpression(AlteredTableInfo *tab, Relation rel, const char *colName,
/* make sure we don't conflict with later attribute modifications */
CommandCounterIncrement();
-
- /*
- * Find everything that depends on the column (constraints, indexes,
- * etc), and record enough information to let us recreate the objects
- * after rewrite.
- */
- RememberAllDependentForRebuilding(tab, AT_SetExpression, rel, attnum, colName);
}
+ /*
+ * Find everything that depends on the column (constraints, indexes, etc),
+ * and record enough information to let us recreate the objects after
+ * rewrite.
+ */
+ RememberAllDependentForRebuilding(tab, AT_SetExpression, rel, attnum, colName);
+
/*
* Drop the dependency records of the GENERATED expression, in particular
* its INTERNAL dependency on the column, which would otherwise cause
diff --git a/src/test/regress/expected/generated_virtual.out b/src/test/regress/expected/generated_virtual.out
index 3b40e15a95a..2c825fa06d4 100644
--- a/src/test/regress/expected/generated_virtual.out
+++ b/src/test/regress/expected/generated_virtual.out
@@ -633,12 +633,22 @@ INSERT INTO gtest20 (a) VALUES (10); -- ok
INSERT INTO gtest20 (a) VALUES (30); -- violates constraint
ERROR: new row for relation "gtest20" violates check constraint "gtest20_b_check"
DETAIL: Failing row contains (30, virtual).
-ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS (a * 100); -- violates constraint (currently not supported)
-ERROR: ALTER TABLE / SET EXPRESSION is not supported for virtual generated columns in tables with check constraints
-DETAIL: Column "b" of relation "gtest20" is a virtual generated column.
-ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS (a * 3); -- ok (currently not supported)
-ERROR: ALTER TABLE / SET EXPRESSION is not supported for virtual generated columns in tables with check constraints
-DETAIL: Column "b" of relation "gtest20" is a virtual generated column.
+ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS (a * 100); -- violates constraint
+ERROR: check constraint "gtest20_b_check" of relation "gtest20" is violated by some row
+ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS (a * 3); -- ok
+--test no table rewrite happen
+ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS (a * 4), ADD COLUMN C int default 11;
+SELECT pa.attnum,pa.attname,attmissingval
+FROM pg_attribute pa
+JOIN pg_attrdef patt ON pa.attrelid = patt.adrelid AND pa.attnum = patt.adnum
+WHERE pa.attrelid = 'gtest20'::regclass
+ORDER BY pa.attnum;
+ attnum | attname | attmissingval
+--------+---------+---------------
+ 2 | b |
+ 3 | c | {11}
+(2 rows)
+
CREATE TABLE gtest20a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL);
INSERT INTO gtest20a (a) VALUES (10);
INSERT INTO gtest20a (a) VALUES (30);
@@ -982,6 +992,15 @@ SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
gtest_child3 | 09-13-2016 | 1 | 4
(3 rows)
+--check constraint was validated based on each partitions's generation expression
+ALTER TABLE gtest_parent ADD CONSTRAINT cc1 CHECK (f3 < 19); --error
+ERROR: check constraint "cc1" of relation "gtest_child" is violated by some row
+ALTER TABLE gtest_parent ADD CONSTRAINT cc1 CHECK (f3 < 66); --error
+ERROR: check constraint "cc1" of relation "gtest_child2" is violated by some row
+ALTER TABLE gtest_parent ADD CONSTRAINT cc1 CHECK (f3 <> 33); --error
+ERROR: check constraint "cc1" of relation "gtest_child3" is violated by some row
+ALTER TABLE gtest_parent ADD CONSTRAINT cc CHECK (f3 < 67); --ok
+ALTER TABLE gtest_parent DROP CONSTRAINT cc;
-- alter generation expression of parent and all its children altogether
ALTER TABLE gtest_parent ALTER COLUMN f3 SET EXPRESSION AS (f2 * 2);
\d gtest_parent
diff --git a/src/test/regress/sql/generated_virtual.sql b/src/test/regress/sql/generated_virtual.sql
index e2b31853e01..4bff3eac035 100644
--- a/src/test/regress/sql/generated_virtual.sql
+++ b/src/test/regress/sql/generated_virtual.sql
@@ -313,8 +313,15 @@ CREATE TABLE gtest20 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTU
INSERT INTO gtest20 (a) VALUES (10); -- ok
INSERT INTO gtest20 (a) VALUES (30); -- violates constraint
-ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS (a * 100); -- violates constraint (currently not supported)
-ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS (a * 3); -- ok (currently not supported)
+ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS (a * 100); -- violates constraint
+ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS (a * 3); -- ok
+--test no table rewrite happen
+ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS (a * 4), ADD COLUMN C int default 11;
+SELECT pa.attnum,pa.attname,attmissingval
+FROM pg_attribute pa
+JOIN pg_attrdef patt ON pa.attrelid = patt.adrelid AND pa.attnum = patt.adnum
+WHERE pa.attrelid = 'gtest20'::regclass
+ORDER BY pa.attnum;
CREATE TABLE gtest20a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL);
INSERT INTO gtest20a (a) VALUES (10);
@@ -532,6 +539,13 @@ ALTER TABLE gtest_child ALTER COLUMN f3 SET EXPRESSION AS (f2 * 10);
\d gtest_child3
SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
+--check constraint was validated based on each partitions's generation expression
+ALTER TABLE gtest_parent ADD CONSTRAINT cc1 CHECK (f3 < 19); --error
+ALTER TABLE gtest_parent ADD CONSTRAINT cc1 CHECK (f3 < 66); --error
+ALTER TABLE gtest_parent ADD CONSTRAINT cc1 CHECK (f3 <> 33); --error
+ALTER TABLE gtest_parent ADD CONSTRAINT cc CHECK (f3 < 67); --ok
+ALTER TABLE gtest_parent DROP CONSTRAINT cc;
+
-- alter generation expression of parent and all its children altogether
ALTER TABLE gtest_parent ALTER COLUMN f3 SET EXPRESSION AS (f2 * 2);
\d gtest_parent
--
2.34.1
view thread (5+ messages)
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], [email protected]
Subject: Re: support ALTER COLUMN SET EXPRESSION over virtual generated column with check constraint
In-Reply-To: <CACJufxExe7+Gh5MKMFiN5xwwmPXaaZC1jjQOFuovv8Q7b1LgFw@mail.gmail.com>
* 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