On Tue, Mar 11, 2025 at 12:17 PM jian he <jian.universal...@gmail.com> 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.
From 1a5f3da55b625911f6e381f11fe569bbb5cde888 Mon Sep 17 00:00:00 2001 From: jian he <jian.universal...@gmail.com> 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/cacjufxh3vetr7orf5rw29gndk3n1wwboe3wdkhyd3ipgrq9...@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
