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: Thu, 24 Apr 2025 10:42:31 +0800
Message-ID: <CACJufxF9vVYcx53yJpC6Oo7Y7_=20=pmv_t-6SBKAJ63bQXhnQ@mail.gmail.com> (raw)
In-Reply-To: <CACJufxH3VETr7orF5rW29GnDk3n1wWbOE3WdkHYd3iPGrQ9E_A@mail.gmail.com>
References: <CACJufxH3VETr7orF5rW29GnDk3n1wWbOE3WdkHYd3iPGrQ9E_A@mail.gmail.com>
On Tue, Mar 11, 2025 at 12:17 PM jian he <[email protected]> wrote:
> hi.
> in summary: ATExecSetExpression, RememberAllDependentForRebuilding
> will do all the work to change the generation expression,
> whether it's virtual or stored.
>
while working on another patch, I found out this can be further simplified.
Thus a new patch is attached.
Attachments:
[text/x-patch] v1-0001-generated-column-set-expression-with-check-constraint.patch (8.1K, ../CACJufxF9vVYcx53yJpC6Oo7Y7_=20=pmv_t-6SBKAJ63bQXhnQ@mail.gmail.com/2-v1-0001-generated-column-set-expression-with-check-constraint.patch)
download | inline diff:
From 1a5f3da55b625911f6e381f11fe569bbb5cde888 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Thu, 24 Apr 2025 10:41:23 +0800
Subject: [PATCH v1 1/1] generated column set expression with check constraint
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
this patch is to support it.
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
---
src/backend/commands/tablecmds.c | 31 ++++++-------------
.../regress/expected/generated_virtual.out | 31 +++++++++++++++----
src/test/regress/sql/generated_virtual.sql | 19 ++++++++++--
3 files changed, 51 insertions(+), 30 deletions(-)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 2705cf11330..dc4eb160f69 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -8601,18 +8601,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 on 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;
@@ -8642,18 +8630,17 @@ ATExecSetExpression(AlteredTableInfo *tab, Relation rel, const char *colName,
* this renders them pointless.
*/
RelationClearMissing(rel);
-
- /* 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);
}
+ /* 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.
+ */
+ 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 6300e7c1d96..ae4cf5abf76 100644
--- a/src/test/regress/expected/generated_virtual.out
+++ b/src/test/regress/expected/generated_virtual.out
@@ -636,12 +636,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 on 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 on 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);
@@ -973,6 +983,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 b4eedeee2fb..b2a8fbc0d71 100644
--- a/src/test/regress/sql/generated_virtual.sql
+++ b/src/test/regress/sql/generated_virtual.sql
@@ -312,8 +312,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);
@@ -523,6 +530,14 @@ 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 (3+ messages) latest in thread
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: <CACJufxF9vVYcx53yJpC6Oo7Y7_=20=pmv_t-6SBKAJ63bQXhnQ@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