hi. rebased only.
From 3ff7dd0fd260a6b3a35f8652f0840ab96bc68c70 Mon Sep 17 00:00:00 2001 From: jian he <jian.universal...@gmail.com> 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/cacjufxh3vetr7orf5rw29gndk3n1wwboe3wdkhyd3ipgrq9...@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
