Subject: Re: [PATCH] Rebuild CHECK constraints after generated column SET EXPRESSION

From: solai v <[email-masked]>
Date: Tue, 19 May 2026 11:01:27 +0530

Hi
I tested v5-0001-recreate-wholerow-dependent-while-ALTER-COLUMN-SET-EXPRESSION.patch
on the current master.The patch applied cleanly and PostgreSQL built
successfully.
I was able to reproduce the issue on unpatched master using a
whole-row CHECK constraint referencing the generated column
through:CHECK ((gtest).b IS NOT NULL).
Without the patch,ALTER COLUMN SET EXPRESSION succeeded even though
existing rows no longer satisfied the constraint.
After applying the patch ,the same ALTER TABLE command correctly
failed with : ERROR:check constraint "whole_row_check" of relation
"gtest" is violated by some row.
The fix and regression coverage look good to me.

Regards,
Solai

---

From: Zsolt Parragi <[email-masked]>
Date: Thu, 28 May 2026 17:58:13 -0500

Looks good, I have one nitpick question:

+			if (isnull)
+				elog(WARNING, "null conbin for relation \"%s\"",
+					 RelationGetRelationName(rel));

Shouldn't this be an error instead? Most existing checks treat it as
an error, and that seems better to me as this shouldn't happen
normally?

---

From: jian he <[email-masked]>
Date: Tue, 2 Jun 2026 08:41:04 +0800

> an error, and that seems better to me as this shouldn't happen
OK.

I changed it to:
> +                       if (isnull)
> +                               elog(ERROR, "null conbin for relation \"%s\"",

I also slightly rephrased the commit message.

--
jian
https://www.enterprisedb.com/
From 7d13a47d8a2b4a7b200d39e02cffaa2ffb883764 Mon Sep 17 00:00:00 2001
From: jian he <[email-masked]>
Date: Tue, 2 Jun 2026 08:35:57 +0800
Subject: [PATCH v6 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,
record_eq does not care about attribute default expression.

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/[email-masked]
commitfest entry: https://commitfest.postgresql.org/patch/6779
---
 src/backend/commands/tablecmds.c              | 185 ++++++++++++++++++
 .../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, 223 insertions(+)

diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index a1845240a98..de563a0c51d 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
@@ -23905,3 +23915,178 @@ 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(ERROR, "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().
+	 * Additionally, there is no need to worry about AFTER triggers; even if
+	 * the trigger were recreated (which it is not), its WHEN qualification
+	 * would 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

---

From: Chao Li <[email-masked]>
Date: Wed, 10 Jun 2026 17:15:38 +0800

> On Jun 2, 2026, at 08:41, jian he <[email-masked]> wrote:
> 

Sorry for not noticing this thread earlier. I also found the same problem while testing the feature and posted a patch in [1]. Thanks to Jian for pointing out this thread.

After reading v6 from this thread, I think my implementation is slightly different:

* v6 adds a new helper, RememberWholeRowDependentForRebuilding(), which reads pg_constraint and pg_index to find objects to rebuild.
* My implementation in [1] reuses RememberAllDependentForRebuilding(), which reads pg_depend, because whole-row dependencies have refobjsubid = 0. My v1 missed the partial-index case, so I added a new helper in v2 to read pg_index.

The main difference is that my patch reuses the existing dependency-based rebuild machinery where possible, and only scans pg_index directly for the partial-index case where pg_depend does not record the whole-row Var dependency. It is also smaller: this thread’s v6 adds 185 lines to tablecmds.c, while my v2 adds 123 lines.

Would you mind taking a look at my implementation in [1]?

BTW, I have also added this problem to the open items list, as I think it should be fixed before releasing v19.

[1] https://www.postgresql.org/message-id/71242FEA-15A3-4073-9FA1-561CA744ACBD%40gmail.com

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/

---

From: Peter Eisentraut <[email-masked]>
Date: Wed, 8 Jul 2026 20:15:54 +0200

> I also slightly rephrased the commit message.

Committed with some code beautification editorialization.