support ALTER COLUMN SET EXPRESSION over virtual generated column with check constraint
Started by jian he10 months ago3 messages
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.
Attachments:
v1-0001-virtual-generated-column-set-expression-with-check-constr.patchtext/x-patch; charset=US-ASCII; name=v1-0001-virtual-generated-column-set-expression-with-check-constr.patchDownload
From fa81ac3a82bfdb0dea52a985eb534a3866b4af7a Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@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
Re: support ALTER COLUMN SET EXPRESSION over virtual generated column with check constraint
On Tue, Mar 11, 2025 at 12:17 PM jian he <jian.universality@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.
Attachments:
v1-0001-generated-column-set-expression-with-check-constraint.patchtext/x-patch; charset=US-ASCII; name=v1-0001-generated-column-set-expression-with-check-constraint.patchDownload
From 1a5f3da55b625911f6e381f11fe569bbb5cde888 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@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/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
Re: support ALTER COLUMN SET EXPRESSION over virtual generated column with check constraint
hi.
rebased only.
Attachments:
v2-0001-allow-change-generated-expression-while-check-constraint-.patchtext/x-patch; charset=US-ASCII; name=v2-0001-allow-change-generated-expression-while-check-constraint-.patchDownload
From 3ff7dd0fd260a6b3a35f8652f0840ab96bc68c70 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@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/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