v5-0001-recreate-wholerow-dependent-while-ALTER-COLUMN-SET-EXPRESSION.patch
text/x-patch
Filename: v5-0001-recreate-wholerow-dependent-while-ALTER-COLUMN-SET-EXPRESSION.patch
Type: text/x-patch
Part: 0
From 0900047db7f67b3cda5b33bd03c1e5eff2c0bca7 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Sat, 16 May 2026 13:57:19 +0800
Subject: [PATCH v5 1/1] recreate wholerow dependent while ALTER COLUMN SET
EXPRESSION
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
When changing the expression of a generated column via ALTER TABLE ALTER COLUMN
SET EXPRESSION, objects that depend on the column via indirect whole-row
references (such as CHECK constraints, indexes) must be handled specially,
because technically pg_depend does not contain such dependencies, see
recordDependencyOnSingleRelExpr, find_expr_references_walker.
ALTER COLUMN SET EXPRESSION does not fundamentally change the table's row type —
it only updates the individual column’s generation expression. As a result,
recreate the affected objects such as indexes, CHECK constraints should be safe.
This is unlike ALTER COLUMN SET DATA TYPE, which fundamentally changes table's
row type.
Demo:
DROP TABLE IF EXISTS r3 CASCADE;
CREATE TABLE r3 (a int, b int GENERATED ALWAYS AS (a * 10) STORED);
ALTER TABLE r3 ADD CONSTRAINT whole_row_check CHECK (r3 IS NOT NULL);
CREATE INDEX r3_idx ON r3 ((r3 = ROW (1, 2)));
-- Constraint is rechecked and index is rebuilt
ALTER TABLE r3 ALTER COLUMN b SET EXPRESSION AS (a * 10::bigint);
related: https://commitfest.postgresql.org/patch/6055
discussion: https://postgr.es/m/CAJTYsWXOkyeDVbzymWc9sKrq7Y_MUv6XJXN4H9GfsBOPd3NJ+w@mail.gmail.com
commitfest entry: https://commitfest.postgresql.org/patch/6779
---
src/backend/commands/tablecmds.c | 184 ++++++++++++++++++
.../regress/expected/generated_stored.out | 9 +
.../regress/expected/generated_virtual.out | 9 +
src/test/regress/sql/generated_stored.sql | 10 +
src/test/regress/sql/generated_virtual.sql | 10 +
5 files changed, 222 insertions(+)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 92b0f38c353..b68dec36553 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -786,6 +786,9 @@ static void ATExecSplitPartition(List **wqueue, AlteredTableInfo *tab,
static List *collectPartitionIndexExtDeps(List *partitionOids);
static void applyPartitionIndexExtDeps(Oid newPartOid, List *extDepState);
static void freePartitionIndexExtDeps(List *extDepState);
+static void RememberWholeRowDependentForRebuilding(AlteredTableInfo *tab, AlterTableType subtype,
+ Relation rel, AttrNumber attnum,
+ const char *colName);
/* ----------------------------------------------------------------
* DefineRelation
@@ -8791,6 +8794,13 @@ ATExecSetExpression(AlteredTableInfo *tab, Relation rel, const char *colName,
*/
RememberAllDependentForRebuilding(tab, AT_SetExpression, rel, attnum, colName);
+ /*
+ * Find whole-row referenced objects that depend on the column
+ * (constraints, indexes, etc.), and record enough information to let us
+ * recreate the objects.
+ */
+ RememberWholeRowDependentForRebuilding(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
@@ -23896,3 +23906,177 @@ ATExecSplitPartition(List **wqueue, AlteredTableInfo *tab, Relation rel,
/* Restore the userid and security context. */
SetUserIdAndSecContext(save_userid, save_sec_context);
}
+
+/*
+ * Record dependencies between objects with whole-row Var references
+ * (indexes, CHECK constraints, etc.) and the relation.
+ *
+ * See also RememberAllDependentForRebuilding, which handles non-whole-row
+ * Var references.
+ *
+ * This function currently applies only to ALTER COLUMN SET EXPRESSION.
+ */
+static void
+RememberWholeRowDependentForRebuilding(AlteredTableInfo *tab, AlterTableType subtype,
+ Relation rel, AttrNumber attnum, const char *colName)
+{
+ Node *expr = NULL;
+ ScanKeyData skey;
+ Relation pg_constraint;
+ Relation pg_index;
+ SysScanDesc conscan;
+ SysScanDesc indscan;
+ HeapTuple constrTuple;
+ HeapTuple indexTuple;
+ Datum exprDatum;
+ char *exprString;
+ bool isnull;
+
+ Assert(subtype == AT_SetExpression);
+
+ /*
+ * Check CHECK constraints with whole-row references first.
+ */
+ if (RelationGetDescr(rel)->constr &&
+ RelationGetDescr(rel)->constr->num_check > 0)
+ {
+ pg_constraint = table_open(ConstraintRelationId, AccessShareLock);
+
+ ScanKeyInit(&skey,
+ Anum_pg_constraint_conrelid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(RelationGetRelid(rel)));
+
+ conscan = systable_beginscan(pg_constraint,
+ ConstraintRelidTypidNameIndexId,
+ true,
+ NULL,
+ 1,
+ &skey);
+ while (HeapTupleIsValid(constrTuple = systable_getnext(conscan)))
+ {
+ Form_pg_constraint conform = (Form_pg_constraint) GETSTRUCT(constrTuple);
+
+ if (conform->contype != CONSTRAINT_CHECK)
+ continue;
+
+ exprDatum = fastgetattr(constrTuple,
+ Anum_pg_constraint_conbin,
+ RelationGetDescr(pg_constraint),
+ &isnull);
+ if (isnull)
+ elog(WARNING, "null conbin for relation \"%s\"",
+ RelationGetRelationName(rel));
+ else
+ {
+ Bitmapset *expr_attrs = NULL;
+
+ exprString = TextDatumGetCString(exprDatum);
+ expr = (Node *) stringToNode(exprString);
+ pfree(exprString);
+
+ /* Find all attributes referenced */
+ pull_varattnos(expr, 1, &expr_attrs);
+
+ /*
+ * If the CHECK constraint contains whole-row reference then
+ * remember it.
+ */
+ if (bms_is_member(InvalidAttrNumber - FirstLowInvalidHeapAttributeNumber,
+ expr_attrs))
+ {
+ RememberConstraintForRebuilding(conform->oid, tab);
+ }
+ }
+ }
+ systable_endscan(conscan);
+ table_close(pg_constraint, AccessShareLock);
+ }
+
+ /*
+ * Now check indexes with whole-row references. Prepare to scan pg_index
+ * for entries having indrelid matching this relation.
+ */
+ ScanKeyInit(&skey,
+ Anum_pg_index_indrelid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(RelationGetRelid(rel)));
+
+ pg_index = table_open(IndexRelationId, AccessShareLock);
+
+ indscan = systable_beginscan(pg_index,
+ IndexIndrelidIndexId,
+ true,
+ NULL,
+ 1,
+ &skey);
+ while (HeapTupleIsValid(indexTuple = systable_getnext(indscan)))
+ {
+ Form_pg_index index = (Form_pg_index) GETSTRUCT(indexTuple);
+
+ if (!heap_attisnull(indexTuple, Anum_pg_index_indexprs, NULL))
+ {
+ Bitmapset *expr_attrs = NULL;
+
+ exprDatum = SysCacheGetAttrNotNull(INDEXRELID, indexTuple,
+ Anum_pg_index_indexprs);
+ exprString = TextDatumGetCString(exprDatum);
+ expr = (Node *) stringToNode(exprString);
+ pfree(exprString);
+
+ /* Find all attributes referenced */
+ pull_varattnos(expr, 1, &expr_attrs);
+
+ /*
+ * If the index expression contains a whole-row reference then
+ * remember it.
+ */
+ if (bms_is_member(InvalidAttrNumber - FirstLowInvalidHeapAttributeNumber,
+ expr_attrs))
+ {
+ RememberIndexForRebuilding(index->indexrelid, tab);
+ continue;
+ }
+ }
+
+ if (!heap_attisnull(indexTuple, Anum_pg_index_indpred, NULL))
+ {
+ Bitmapset *expr_attrs = NULL;
+
+ exprDatum = SysCacheGetAttrNotNull(INDEXRELID, indexTuple,
+ Anum_pg_index_indpred);
+ exprString = TextDatumGetCString(exprDatum);
+ expr = (Node *) stringToNode(exprString);
+ pfree(exprString);
+
+ /* Find all attributes referenced */
+ pull_varattnos(expr, 1, &expr_attrs);
+
+ /*
+ * If the index predicate expression contains a whole-row
+ * reference then remember it.
+ */
+ if (bms_is_member(InvalidAttrNumber - FirstLowInvalidHeapAttributeNumber,
+ expr_attrs))
+ {
+ RememberIndexForRebuilding(index->indexrelid, tab);
+ }
+ }
+ }
+ systable_endscan(indscan);
+ table_close(pg_index, AccessShareLock);
+
+ /*
+ * For ALTER TABLE SET EXPRESSION:
+ *
+ * 1.No need to check trigger with whole-row references. Creation of
+ * BEFORE triggers with whole-row Vars referencing (some column is
+ * generated column) is disallowed; see CreateTriggerFiringOn(). There is
+ * also no need to worry about AFTER triggers because, even if the trigger
+ * is recreated, its WHEN qualification will remain unchanged.
+ *
+ * 2. No need to recheck policies with whole-row references, since we do
+ * not recreate and re-evaluate the policy condition when a dependent
+ * column's generated expression changes.
+ */
+}
diff --git a/src/test/regress/expected/generated_stored.out b/src/test/regress/expected/generated_stored.out
index 7866ae0ebbe..469d995a742 100644
--- a/src/test/regress/expected/generated_stored.out
+++ b/src/test/regress/expected/generated_stored.out
@@ -688,6 +688,15 @@ INSERT INTO gtest20c VALUES (1); -- ok
INSERT INTO gtest20c VALUES (NULL); -- fails
ERROR: new row for relation "gtest20c" violates check constraint "whole_row_check"
DETAIL: Failing row contains (null, null).
+ALTER TABLE gtest20c ALTER COLUMN b SET EXPRESSION AS (NULL::int); -- violates constraint
+ERROR: check constraint "whole_row_check" of relation "gtest20c" is violated by some row
+-- index with whole-row reference needs rebuild
+CREATE TABLE gtest20d (a int, b int GENERATED ALWAYS AS (a * 2) STORED);
+INSERT INTO gtest20d VALUES (1), (1);
+CREATE INDEX gtest20d_idx1 ON gtest20d (a) WHERE gtest20d = ROW (1, 2);
+ALTER TABLE gtest20d ALTER COLUMN b SET EXPRESSION AS ( a * 2::bigint); -- index rebuild
+CREATE INDEX gtest20d_idx2 ON gtest20d ((gtest20d = ROW (1, 2)));
+ALTER TABLE gtest20d ALTER COLUMN b SET EXPRESSION AS ( a * 3); -- index rebuild
-- not-null constraints
CREATE TABLE gtest21a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) STORED NOT NULL);
INSERT INTO gtest21a (a) VALUES (1); -- ok
diff --git a/src/test/regress/expected/generated_virtual.out b/src/test/regress/expected/generated_virtual.out
index 24d5dbf46ca..cc13a3c318a 100644
--- a/src/test/regress/expected/generated_virtual.out
+++ b/src/test/regress/expected/generated_virtual.out
@@ -694,6 +694,15 @@ INSERT INTO gtest20c VALUES (1); -- ok
INSERT INTO gtest20c VALUES (NULL); -- fails
ERROR: new row for relation "gtest20c" violates check constraint "whole_row_check"
DETAIL: Failing row contains (null, virtual).
+ALTER TABLE gtest20c ALTER COLUMN b SET EXPRESSION AS (NULL::int); -- violates constraint
+ERROR: check constraint "whole_row_check" of relation "gtest20c" is violated by some row
+-- index with whole-row reference needs rebuild
+CREATE TABLE gtest20d (a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL);
+INSERT INTO gtest20d VALUES (1), (1);
+CREATE INDEX gtest20d_idx1 ON gtest20d (a) WHERE gtest20d = ROW (1, 2);
+ALTER TABLE gtest20d ALTER COLUMN b SET EXPRESSION AS ( a * 2::bigint); -- index rebuild
+CREATE INDEX gtest20d_idx2 ON gtest20d ((gtest20d = ROW (1, 2)));
+ALTER TABLE gtest20d ALTER COLUMN b SET EXPRESSION AS ( a * 3); -- index rebuild
-- not-null constraints
CREATE TABLE gtest21a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) VIRTUAL NOT NULL);
INSERT INTO gtest21a (a) VALUES (1); -- ok
diff --git a/src/test/regress/sql/generated_stored.sql b/src/test/regress/sql/generated_stored.sql
index 6746cd4632b..c91fa144c90 100644
--- a/src/test/regress/sql/generated_stored.sql
+++ b/src/test/regress/sql/generated_stored.sql
@@ -341,6 +341,16 @@ CREATE TABLE gtest20c (a int, b int GENERATED ALWAYS AS (a * 2) STORED);
ALTER TABLE gtest20c ADD CONSTRAINT whole_row_check CHECK (gtest20c IS NOT NULL);
INSERT INTO gtest20c VALUES (1); -- ok
INSERT INTO gtest20c VALUES (NULL); -- fails
+ALTER TABLE gtest20c ALTER COLUMN b SET EXPRESSION AS (NULL::int); -- violates constraint
+
+-- index with whole-row reference needs rebuild
+CREATE TABLE gtest20d (a int, b int GENERATED ALWAYS AS (a * 2) STORED);
+INSERT INTO gtest20d VALUES (1), (1);
+CREATE INDEX gtest20d_idx1 ON gtest20d (a) WHERE gtest20d = ROW (1, 2);
+
+ALTER TABLE gtest20d ALTER COLUMN b SET EXPRESSION AS ( a * 2::bigint); -- index rebuild
+CREATE INDEX gtest20d_idx2 ON gtest20d ((gtest20d = ROW (1, 2)));
+ALTER TABLE gtest20d ALTER COLUMN b SET EXPRESSION AS ( a * 3); -- index rebuild
-- not-null constraints
CREATE TABLE gtest21a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) STORED NOT NULL);
diff --git a/src/test/regress/sql/generated_virtual.sql b/src/test/regress/sql/generated_virtual.sql
index 9c2bb6590b3..5b75f2ebf23 100644
--- a/src/test/regress/sql/generated_virtual.sql
+++ b/src/test/regress/sql/generated_virtual.sql
@@ -347,6 +347,16 @@ CREATE TABLE gtest20c (a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL);
ALTER TABLE gtest20c ADD CONSTRAINT whole_row_check CHECK (gtest20c IS NOT NULL);
INSERT INTO gtest20c VALUES (1); -- ok
INSERT INTO gtest20c VALUES (NULL); -- fails
+ALTER TABLE gtest20c ALTER COLUMN b SET EXPRESSION AS (NULL::int); -- violates constraint
+
+-- index with whole-row reference needs rebuild
+CREATE TABLE gtest20d (a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL);
+INSERT INTO gtest20d VALUES (1), (1);
+CREATE INDEX gtest20d_idx1 ON gtest20d (a) WHERE gtest20d = ROW (1, 2);
+
+ALTER TABLE gtest20d ALTER COLUMN b SET EXPRESSION AS ( a * 2::bigint); -- index rebuild
+CREATE INDEX gtest20d_idx2 ON gtest20d ((gtest20d = ROW (1, 2)));
+ALTER TABLE gtest20d ALTER COLUMN b SET EXPRESSION AS ( a * 3); -- index rebuild
-- not-null constraints
CREATE TABLE gtest21a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) VIRTUAL NOT NULL);
--
2.34.1