hi.
in RememberAllDependentForRebuilding
while (HeapTupleIsValid(depTup = systable_getnext(scan)))
{
if(subtype == AT_SetExpression)
elog(INFO, "foundObject.classId:%d", foundObject.classId);
}
Then do the regress test on generated_stored.sql
I found out only constraints and indexes will be rebuilt
while we are doing ALTER TABLE ALTER COLUMN SET EXPRESSION.
we can also see RememberAllDependentForRebuilding handling of:
case RelationRelationId:
case AttrDefaultRelationId:
case ConstraintRelationId:
RememberAllDependentForRebuilding record
AlteredTableInfo->changedConstraintOids, AlteredTableInfo->changedIndexOids.
ATPostAlterTypeCleanup will construct steps to rebuild these
constraints over the generated column.
and if these constraints are successfully installed,
AlteredTableInfo->constraints will be populated.
then in phase3 ATRewriteTable will do the scan or rewrite.
in summary: ATExecSetExpression, RememberAllDependentForRebuilding
will do all the work to change the generation expression,
whether it's virtual or stored.
we didn't support virtual generated columns over domain with check constraint.
we also didn't support index over virtual generated columns.
to support change generation expressions for virtual generated columns
over check constraints,
the code seems not hard.
From fa81ac3a82bfdb0dea52a985eb534a3866b4af7a Mon Sep 17 00:00:00 2001
From: jian he <jian.universal...@gmail.com>
Date: Tue, 11 Mar 2025 12:17:00 +0800
Subject: [PATCH v1 1/1] virtual generated column set expression with check
constraint
currently, if virtual generated column have check constraints over it
(we currently not supported index on 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/
---
src/backend/commands/tablecmds.c | 28 +++++++------
.../regress/expected/generated_virtual.out | 39 ++++++++++++-------
src/test/regress/sql/generated_virtual.sql | 19 +++++++--
3 files changed, 58 insertions(+), 28 deletions(-)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 1f870982559..079080700dc 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -8464,6 +8464,7 @@ ATExecSetExpression(AlteredTableInfo *tab, Relation rel, const char *colName,
Expr *defval;
NewColumnValue *newval;
RawColumnDefault *rawEnt;
+ bool rescan = false;
tuple = SearchSysCacheAttName(RelationGetRelid(rel), colName);
if (!HeapTupleIsValid(tuple))
@@ -8488,17 +8489,7 @@ 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))));
+ rescan = (attgenerated == ATTRIBUTE_GENERATED_VIRTUAL);
/*
* We need to prevent this because a change of expression could affect a
@@ -8538,6 +8529,21 @@ ATExecSetExpression(AlteredTableInfo *tab, Relation rel, const char *colName,
RememberAllDependentForRebuilding(tab, AT_SetExpression, rel, attnum, colName);
}
+ if (rescan)
+ {
+ Assert(!rewrite);
+
+ /* 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 recan.
+ */
+ 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 7ef05f45be7..8d609c7c66e 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);
@@ -915,14 +925,15 @@ Partition of: gtest_parent FOR VALUES FROM ('08-01-2016') TO ('09-01-2016')
f3 | bigint | | | generated always as (f2 * 33)
Partition of: gtest_parent FOR VALUES FROM ('09-01-2016') TO ('10-01-2016')
-SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
- tableoid | f1 | f2 | f3
---------------+------------+----+----
- gtest_child | 07-15-2016 | 2 | 8
- gtest_child2 | 08-15-2016 | 3 | 12
- gtest_child3 | 09-13-2016 | 1 | 4
-(3 rows)
-
+--error. check constraint was validated for each partitions's generation expression
+ALTER TABLE gtest_parent ADD CHECK (f3 < 21);
+ERROR: check constraint "gtest_parent_f3_check" of relation "gtest_child2" is violated by some row
+ALTER TABLE gtest_parent ADD CHECK (f3 < 33);
+ERROR: check constraint "gtest_parent_f3_check" of relation "gtest_child2" is violated by some row
+ALTER TABLE gtest_parent ADD CHECK (f3 < 66);
+ERROR: check constraint "gtest_parent_f3_check" of relation "gtest_child2" 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 dab8c92ef99..dd4dd4d77e3 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);
@@ -488,8 +495,14 @@ ALTER TABLE gtest_child ALTER COLUMN f3 SET EXPRESSION AS (f2 * 10);
\d gtest_child
\d gtest_child2
\d gtest_child3
-SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
+--error. check constraint was validated for each partitions's generation expression
+ALTER TABLE gtest_parent ADD CHECK (f3 < 21);
+ALTER TABLE gtest_parent ADD CHECK (f3 < 33);
+ALTER TABLE gtest_parent ADD CHECK (f3 < 66);
+
+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
