Subject: let ALTER TABLE DROP COLUMN drop whole-row referenced object

From: jian he <[email-masked]>
Date: Mon, 8 Sep 2025 10:53:00 +0800

hi.

CREATE TABLE ts (a int, c int, b int
    constraint cc check((ts = ROW(1,1,1))),
    constraint cc1 check((ts.a = 1)));
CREATE INDEX tsi on ts (a) where a = 1;
CREATE INDEX tsi2 on ts ((a is null));
CREATE INDEX tsi3 on ts ((ts is null));
CREATE INDEX tsi4 on ts (b) where ts is not null;

in the master, ``ALTER TABLE ts DROP COLUMN a;``
will not drop constraint cc, index tsi3, tsi4;

with the attached patch,
``ALTER TABLE ts DROP COLUMN a;``
will drop above all indexes on the table "ts" and also remove the
constraints "cc" and "cc1".

as per the documentation[1], quote:
"""
DROP COLUMN [ IF EXISTS ]
This form drops a column from a table. Indexes and table constraints involving
the column will be automatically dropped as well.
"""

so I think it's expected behavior to drop the entire
whole-row referenced indexes and constraints.

[1] https://www.postgresql.org/docs/devel/sql-altertable.html#SQL-ALTERTABLE-DESC-DROP-COLUMN
From 82f795b26e9438990680f0c56ff82853504e80eb Mon Sep 17 00:00:00 2001
From: jian he <[email-masked]>
Date: Mon, 8 Sep 2025 02:06:18 +0800
Subject: [PATCH v1 1/1] let ALTER TABLE DROP COLUMN drop wholerow referenced
 object

CREATE TABLE ts (a int, c int, b int
    constraint cc check((ts = ROW(1,1,1))),
    constraint cc1 check((ts.a = 1)));

CREATE INDEX tsi on ts (a) where a = 1;
CREATE INDEX tsi2 on ts ((a is null));
CREATE INDEX tsi3 on ts ((ts is null));
CREATE INDEX tsi4 on ts (b) where ts is not null;

ALTER TABLE ts DROP COLUMN a;
will drop above all indexes on the table ts and also remove the constraints cc
and cc1.

discussion: https://postgr.es/m/
---
 src/backend/commands/tablecmds.c          | 122 ++++++++++++++++++++++
 src/test/regress/expected/constraints.out |  17 +++
 src/test/regress/expected/indexing.out    |  25 +++++
 src/test/regress/sql/constraints.sql      |  11 ++
 src/test/regress/sql/indexing.sql         |   9 ++
 5 files changed, 184 insertions(+)

diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 082a3575d62..94224ce2c8d 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -9257,6 +9257,9 @@ ATExecDropColumn(List **wqueue, Relation rel, const char *colName,
 	List	   *children;
 	ObjectAddress object;
 	bool		is_expr;
+	Node	   *expr;
+	List	   *indexlist = NIL;
+	TupleConstr *constr = RelationGetDescr(rel)->constr;
 
 	/* At top level, permission check was done in ATPrepCmd, else do it */
 	if (recursing)
@@ -9329,6 +9332,125 @@ ATExecDropColumn(List **wqueue, Relation rel, const char *colName,
 
 	ReleaseSysCache(tuple);
 
+	/* also drop the check constraint that references whole-row */
+	if (constr && constr->num_check > 0)
+	{
+		ConstrCheck *check = constr->check;
+
+		for (int i = 0; i < constr->num_check; i++)
+		{
+			Bitmapset  *expr_attrs = NULL;
+			char	   *constr_name = check[i].ccname;
+
+			expr = stringToNode(check[i].ccbin);
+
+			/* Find all attributes referenced */
+			pull_varattnos(expr, 1, &expr_attrs);
+
+			if (bms_is_member(0 - FirstLowInvalidHeapAttributeNumber, expr_attrs))
+			{
+				Relation	conDesc;
+				SysScanDesc conscan;
+				ScanKeyData skey[3];
+				HeapTuple	contuple;
+
+				/* Search for a pg_constraint entry with same name and relation */
+				conDesc = table_open(ConstraintRelationId, AccessShareLock);
+
+				ScanKeyInit(&skey[0],
+							Anum_pg_constraint_conrelid,
+							BTEqualStrategyNumber, F_OIDEQ,
+							ObjectIdGetDatum(RelationGetRelid(rel)));
+				ScanKeyInit(&skey[1],
+							Anum_pg_constraint_contypid,
+							BTEqualStrategyNumber, F_OIDEQ,
+							ObjectIdGetDatum(InvalidOid));
+				ScanKeyInit(&skey[2],
+							Anum_pg_constraint_conname,
+							BTEqualStrategyNumber, F_NAMEEQ,
+							CStringGetDatum(constr_name));
+
+				conscan = systable_beginscan(conDesc, ConstraintRelidTypidNameIndexId, true,
+											 NULL, 3, skey);
+
+				if (!HeapTupleIsValid(contuple = systable_getnext(conscan)))
+					elog(ERROR, "constraint \"%s\" of relation \"%s\" does not exist",
+						 constr_name, RelationGetRelationName(rel));
+
+				/* Add object to delete */
+				object.classId = ConstraintRelationId;
+				object.objectId = ((Form_pg_constraint) GETSTRUCT(contuple))->oid;
+				object.objectSubId = 0;
+				add_exact_object_address(&object, addrs);
+
+				systable_endscan(conscan);
+				table_close(conDesc, AccessShareLock);
+			}
+		}
+	}
+
+	/* also drop the indexes that references whole-row */
+	indexlist = RelationGetIndexList(rel);
+	foreach_oid(indexoid, indexlist)
+	{
+		HeapTuple	indexTuple;
+		Form_pg_index indexStruct;
+		Node	   *node;
+
+		indexTuple = SearchSysCache1(INDEXRELID, ObjectIdGetDatum(indexoid));
+		if (!HeapTupleIsValid(indexTuple))
+			elog(ERROR, "cache lookup failed for index %u", indexoid);
+		indexStruct = (Form_pg_index) GETSTRUCT(indexTuple);
+
+		if (!heap_attisnull(indexTuple, Anum_pg_index_indpred, NULL))
+		{
+			Datum		predDatum;
+			char	   *predString;
+			Bitmapset  *expr_attrs = NULL;
+
+			/* Convert text string to node tree */
+			predDatum = SysCacheGetAttrNotNull(INDEXRELID, indexTuple,
+											   Anum_pg_index_indpred);
+			predString = TextDatumGetCString(predDatum);
+			node = (Node *) stringToNode(predString);
+			pfree(predString);
+
+			pull_varattnos(node, 1, &expr_attrs);
+			if (bms_is_member(0 - FirstLowInvalidHeapAttributeNumber, expr_attrs))
+			{
+				object.classId = RelationRelationId;
+				object.objectId = indexStruct->indexrelid;
+				object.objectSubId = 0;
+				add_exact_object_address(&object, addrs);
+			}
+		}
+		else if (!heap_attisnull(indexTuple, Anum_pg_index_indexprs, NULL))
+		{
+			Datum		exprDatum;
+			char	   *exprString;
+			Bitmapset  *expr_attrs = NULL;
+
+			/* Convert text string to node tree */
+			exprDatum = SysCacheGetAttrNotNull(INDEXRELID, indexTuple,
+											   Anum_pg_index_indexprs);
+			exprString = TextDatumGetCString(exprDatum);
+			node = (Node *) stringToNode(exprString);
+			pfree(exprString);
+
+			pull_varattnos(node, 1, &expr_attrs);
+
+			/* Add object to delete */
+			if (bms_is_member(0 - FirstLowInvalidHeapAttributeNumber, expr_attrs))
+			{
+				object.classId = RelationRelationId;
+				object.objectId = indexStruct->indexrelid;
+				object.objectSubId = 0;
+				add_exact_object_address(&object, addrs);
+			}
+		}
+		ReleaseSysCache(indexTuple);
+	}
+
 	/*
 	 * Propagate to children as appropriate.  Unlike most other ALTER
 	 * routines, we have to do this one level of recursion at a time; we can't
diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out
index 3590d3274f0..ce2fb02971f 100644
--- a/src/test/regress/expected/constraints.out
+++ b/src/test/regress/expected/constraints.out
@@ -254,6 +254,23 @@ ERROR:  system column "ctid" reference in check constraint is invalid
 LINE 3:       CHECK (NOT (is_capital AND ctid::text = 'sys_col_check...
                                          ^
 --
+-- Drop column also drop the associated Check constraints and whole-row referenced check constraint
+--
+CREATE TABLE DROP_COL_CHECK_TBL (
+    city text, state text, is_capital bool, altitude int,
+    CONSTRAINT cc CHECK (city is not null),
+    CONSTRAINT cc1 CHECK (DROP_COL_CHECK_TBL is not null));
+ALTER TABLE DROP_COL_CHECK_TBL DROP COLUMN city;
+\d DROP_COL_CHECK_TBL
+           Table "public.drop_col_check_tbl"
+   Column   |  Type   | Collation | Nullable | Default 
+------------+---------+-----------+----------+---------
+ state      | text    |           |          | 
+ is_capital | boolean |           |          | 
+ altitude   | integer |           |          | 
+
+DROP TABLE DROP_COL_CHECK_TBL;
+--
 -- Check inheritance of defaults and constraints
 --
 CREATE TABLE INSERT_CHILD (cx INT default 42,
diff --git a/src/test/regress/expected/indexing.out b/src/test/regress/expected/indexing.out
index 4d29fb85293..ec32543c1e4 100644
--- a/src/test/regress/expected/indexing.out
+++ b/src/test/regress/expected/indexing.out
@@ -654,6 +654,31 @@ alter table idxpart2 drop column c;
  b      | integer |           |          | 
 
 drop table idxpart, idxpart2;
+create table idxpart (a int, b int, c int);
+create index on idxpart(c);
+create index on idxpart((idxpart is not null));
+create index on idxpart(a) where idxpart is not null;
+\d idxpart
+              Table "public.idxpart"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ a      | integer |           |          | 
+ b      | integer |           |          | 
+ c      | integer |           |          | 
+Indexes:
+    "idxpart_a_idx" btree (a) WHERE idxpart.* IS NOT NULL
+    "idxpart_c_idx" btree (c)
+    "idxpart_expr_idx" btree ((idxpart.* IS NOT NULL))
+
+alter table idxpart drop column c;
+\d idxpart
+              Table "public.idxpart"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ a      | integer |           |          | 
+ b      | integer |           |          | 
+
+drop table idxpart;
 -- Verify that expression indexes inherit correctly
 create table idxpart (a int, b int) partition by range (a);
 create table idxpart1 (like idxpart);
diff --git a/src/test/regress/sql/constraints.sql b/src/test/regress/sql/constraints.sql
index 1f6dc8fd69f..545f8fa17a3 100644
--- a/src/test/regress/sql/constraints.sql
+++ b/src/test/regress/sql/constraints.sql
@@ -165,6 +165,17 @@ CREATE TABLE SYS_COL_CHECK_TBL (city text, state text, is_capital bool,
                   altitude int,
 				  CHECK (NOT (is_capital AND ctid::text = 'sys_col_check_tbl')));
 
+--
+-- Drop column also drop the associated Check constraints and whole-row referenced check constraint
+--
+CREATE TABLE DROP_COL_CHECK_TBL (
+    city text, state text, is_capital bool, altitude int,
+    CONSTRAINT cc CHECK (city is not null),
+    CONSTRAINT cc1 CHECK (DROP_COL_CHECK_TBL is not null));
+ALTER TABLE DROP_COL_CHECK_TBL DROP COLUMN city;
+\d DROP_COL_CHECK_TBL
+DROP TABLE DROP_COL_CHECK_TBL;
+
 --
 -- Check inheritance of defaults and constraints
 --
diff --git a/src/test/regress/sql/indexing.sql b/src/test/regress/sql/indexing.sql
index b5cb01c2d70..825625b01b6 100644
--- a/src/test/regress/sql/indexing.sql
+++ b/src/test/regress/sql/indexing.sql
@@ -295,6 +295,15 @@ alter table idxpart2 drop column c;
 \d idxpart2
 drop table idxpart, idxpart2;
 
+create table idxpart (a int, b int, c int);
+create index on idxpart(c);
+create index on idxpart((idxpart is not null));
+create index on idxpart(a) where idxpart is not null;
+\d idxpart
+alter table idxpart drop column c;
+\d idxpart
+drop table idxpart;
+
 -- Verify that expression indexes inherit correctly
 create table idxpart (a int, b int) partition by range (a);
 create table idxpart1 (like idxpart);
-- 
2.34.1

---

From: jian he <[email-masked]>
Date: Tue, 9 Sep 2025 11:12:36 +0800

hi.
I found a new way to solve this problem.

for constraint cc, there is no extra dependency between column a and
constraint cc.
see find_expr_references_walker below comments:

        /*
         * A whole-row Var references no specific columns, so adds no new
         * dependency.  (We assume that there is a whole-table dependency
         * arising from each underlying rangetable entry.  While we could
         * record such a dependency when finding a whole-row Var that
         * references a relation directly, it's quite unclear how to extend
         * that to whole-row Vars for JOINs, so it seems better to leave the
         * responsibility with the range table.  Note that this poses some
         * risks for identifying dependencies of stand-alone expressions:
         * whole-table references may need to be created separately.)
         */
Ideally, for constraint "cc", there should be three pg_depend entries
corresponding to column a, column b, and column c, but those entries are
missing, but we didn't.

so, in ATExecDropColumn, instead of adding another object to the deletion
list (``add_exact_object_address(&object, addrs)``) like what we did v1,
we first call recordDependencyOn to explicitly record the dependency between
constraint cc and column a, and then rely on performMultipleDeletions to handle
the deletion properly

demo:
CREATE TABLE ts (a int, c int, b int
    constraint cc check((ts = ROW(1,1,1))),
    constraint cc1 check((ts.a = 1)));
CREATE INDEX tsi on ts (a) where a = 1;
CREATE INDEX tsi2 on ts ((a is null));
CREATE INDEX tsi3 on ts ((ts is null));
CREATE INDEX tsi4 on ts (b) where ts is not null;
CREATE POLICY p1 ON ts USING (ts >= ROW(1,1,1));
CREATE POLICY p2 ON ts USING (ts.a = 1);

ALTER TABLE ts DROP COLUMN a CASCADE;
will drop above all indexes, constraints and policies on the table ts.
From 0bfe5dd6cc313eac75a81aaf122ef090fdd26a98 Mon Sep 17 00:00:00 2001
From: jian he <[email-masked]>
Date: Tue, 9 Sep 2025 10:54:56 +0800
Subject: [PATCH v2 1/1] ALTER TABLE DROP COLUMN drop wholerow referenced
 object

CREATE TABLE ts (a int, c int, b int
    constraint cc check((ts = ROW(1,1,1))),
    constraint cc1 check((ts.a = 1)));
CREATE INDEX tsi on ts (a) where a = 1;
CREATE INDEX tsi2 on ts ((a is null));
CREATE INDEX tsi3 on ts ((ts is null));
CREATE INDEX tsi4 on ts (b) where ts is not null;
CREATE POLICY p1 ON ts USING (ts >= ROW(1,1,1));
CREATE POLICY p2 ON ts USING (ts.a = 1);

ALTER TABLE ts DROP COLUMN a CASCADE;
will drop above all indexes, constraints and policies on the table ts.

now
\d ts
                 Table "public.ts"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 c      | integer |           |          |
 b      | integer |           |          |

discussion: https://postgr.es/m/[email-masked]
---
 src/backend/commands/tablecmds.c          | 206 ++++++++++++++++++++++
 src/test/regress/expected/constraints.out |  17 ++
 src/test/regress/expected/indexing.out    |  25 +++
 src/test/regress/expected/rowsecurity.out |  21 +++
 src/test/regress/sql/constraints.sql      |  11 ++
 src/test/regress/sql/indexing.sql         |   9 +
 src/test/regress/sql/rowsecurity.sql      |   9 +
 7 files changed, 298 insertions(+)

diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 082a3575d62..b21f20ca8fd 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -9256,7 +9256,15 @@ ATExecDropColumn(List **wqueue, Relation rel, const char *colName,
 	AttrNumber	attnum;
 	List	   *children;
 	ObjectAddress object;
+	ObjectAddress tmpobject;
 	bool		is_expr;
+	Node	   *expr;
+	List	   *indexlist = NIL;
+	TupleConstr *constr = RelationGetDescr(rel)->constr;
+	Relation	pg_policy;
+	ScanKeyData skey[1];
+	SysScanDesc sscan;
+	HeapTuple	policy_tuple;
 
 	/* At top level, permission check was done in ATPrepCmd, else do it */
 	if (recursing)
@@ -9329,6 +9337,204 @@ ATExecDropColumn(List **wqueue, Relation rel, const char *colName,
 
 	ReleaseSysCache(tuple);
 
+	tmpobject.classId = RelationRelationId;
+	tmpobject.objectId = RelationGetRelid(rel);
+	tmpobject.objectSubId = attnum;
+
+	/*
+	 * ALTER TABLE DROP COLUMN also need drop indexes or constraints that
+	 * include whole-row reference expressions. However, there is no direct
+	 * dependency between whole-row Var references and individual table columns,
+	 * and adding such dependencies would lead to catalog bloat (see
+	 * find_expr_references_walker).
+	 * Therefore, here, we explicitly use recordDependencyOn to create a
+	 * dependency between the table column and any constraint or index that
+	 * contains whole-row Var references.
+	 * Later performMultipleDeletions will do the job.
+	*/
+	if (constr && constr->num_check > 0)
+	{
+		ConstrCheck *check = constr->check;
+
+		for (int i = 0; i < constr->num_check; i++)
+		{
+			Bitmapset  *expr_attrs = NULL;
+			char	   *constr_name = check[i].ccname;
+
+			expr = stringToNode(check[i].ccbin);
+
+			/* Find all attributes referenced */
+			pull_varattnos(expr, 1, &expr_attrs);
+
+			if (bms_is_member(0 - FirstLowInvalidHeapAttributeNumber, expr_attrs))
+			{
+				Relation	conDesc;
+				SysScanDesc conscan;
+				ScanKeyData skey[3];
+				HeapTuple	contuple;
+
+				/* Search for a pg_constraint entry with same name and relation */
+				conDesc = table_open(ConstraintRelationId, AccessShareLock);
+
+				ScanKeyInit(&skey[0],
+							Anum_pg_constraint_conrelid,
+							BTEqualStrategyNumber, F_OIDEQ,
+							ObjectIdGetDatum(RelationGetRelid(rel)));
+				ScanKeyInit(&skey[1],
+							Anum_pg_constraint_contypid,
+							BTEqualStrategyNumber, F_OIDEQ,
+							ObjectIdGetDatum(InvalidOid));
+				ScanKeyInit(&skey[2],
+							Anum_pg_constraint_conname,
+							BTEqualStrategyNumber, F_NAMEEQ,
+							CStringGetDatum(constr_name));
+
+				conscan = systable_beginscan(conDesc, ConstraintRelidTypidNameIndexId, true,
+											 NULL, 3, skey);
+				if (!HeapTupleIsValid(contuple = systable_getnext(conscan)))
+					elog(ERROR, "constraint \"%s\" of relation \"%s\" does not exist",
+						 constr_name, RelationGetRelationName(rel));
+
+				object.classId = ConstraintRelationId;
+				object.objectId = ((Form_pg_constraint) GETSTRUCT(contuple))->oid;
+				object.objectSubId = 0;
+
+				/* record dependency for constraints that references whole-row */
+				recordDependencyOn(&object, &tmpobject, DEPENDENCY_AUTO);
+
+				systable_endscan(conscan);
+				table_close(conDesc, AccessShareLock);
+			}
+		}
+	}
+
+	indexlist = RelationGetIndexList(rel);
+	foreach_oid(indexoid, indexlist)
+	{
+		HeapTuple	indexTuple;
+		Form_pg_index indexStruct;
+		Node	   *node;
+		bool		found_whole_row = false;
+
+		indexTuple = SearchSysCache1(INDEXRELID, ObjectIdGetDatum(indexoid));
+		if (!HeapTupleIsValid(indexTuple))
+			elog(ERROR, "cache lookup failed for index %u", indexoid);
+		indexStruct = (Form_pg_index) GETSTRUCT(indexTuple);
+
+		if (!heap_attisnull(indexTuple, Anum_pg_index_indpred, NULL))
+		{
+			Datum		predDatum;
+			char	   *predString;
+			Bitmapset  *expr_attrs = NULL;
+
+			/* Convert text string to node tree */
+			predDatum = SysCacheGetAttrNotNull(INDEXRELID, indexTuple,
+											   Anum_pg_index_indpred);
+			predString = TextDatumGetCString(predDatum);
+			node = (Node *) stringToNode(predString);
+			pfree(predString);
+
+			pull_varattnos(node, 1, &expr_attrs);
+			if (bms_is_member(0 - FirstLowInvalidHeapAttributeNumber, expr_attrs))
+			{
+				object.classId = RelationRelationId;
+				object.objectId = indexStruct->indexrelid;
+				object.objectSubId = 0;
+				/* record dependency for indexes that references whole-row */
+				recordDependencyOn(&object, &tmpobject, DEPENDENCY_AUTO);
+				found_whole_row = true;
+			}
+		}
+		
+		if (!found_whole_row && !heap_attisnull(indexTuple, Anum_pg_index_indexprs, NULL))
+		{
+			Datum		exprDatum;
+			char	   *exprString;
+			Bitmapset  *expr_attrs = NULL;
+
+			/* Convert text string to node tree */
+			exprDatum = SysCacheGetAttrNotNull(INDEXRELID, indexTuple,
+											   Anum_pg_index_indexprs);
+			exprString = TextDatumGetCString(exprDatum);
+			node = (Node *) stringToNode(exprString);
+			pfree(exprString);
+
+			pull_varattnos(node, 1, &expr_attrs);
+
+			if (bms_is_member(0 - FirstLowInvalidHeapAttributeNumber, expr_attrs))
+			{
+				object.classId = RelationRelationId;
+				object.objectId = indexStruct->indexrelid;
+				object.objectSubId = 0;
+				/* record dependency for indexes that references whole-row */
+				recordDependencyOn(&object, &tmpobject, DEPENDENCY_AUTO);
+			}
+		}
+		ReleaseSysCache(indexTuple);
+	}
+
+	pg_policy = table_open(PolicyRelationId, AccessShareLock);
+	ScanKeyInit(&skey[0],
+				Anum_pg_policy_polrelid,
+				BTEqualStrategyNumber, F_OIDEQ,
+				ObjectIdGetDatum(RelationGetRelid(rel)));
+	sscan = systable_beginscan(pg_policy,
+							   PolicyPolrelidPolnameIndexId, true, NULL, 1,
+							   skey);
+
+	while (HeapTupleIsValid(policy_tuple = systable_getnext(sscan)))
+	{
+		Datum		datum;
+		bool		isnull;
+		char	   *str_value;
+		Bitmapset  *expr_attrs = NULL;
+
+		Form_pg_policy policy = (Form_pg_policy) GETSTRUCT(policy_tuple);
+		/* Get policy qual */
+		datum = heap_getattr(policy_tuple, Anum_pg_policy_polqual,
+							 RelationGetDescr(pg_policy), &isnull);
+		if (!isnull)
+		{
+			str_value = TextDatumGetCString(datum);
+			expr = (Node *) stringToNode(str_value);
+			pfree(str_value);
+
+			pull_varattnos(expr, 1, &expr_attrs);
+			if (bms_is_member(0 - FirstLowInvalidHeapAttributeNumber, expr_attrs))
+			{
+				object.classId = PolicyRelationId;
+				object.objectId = policy->oid;
+				object.objectSubId = 0;
+
+				recordDependencyOn(&object, &tmpobject, DEPENDENCY_NORMAL);
+				/* we only need one dependency entry */
+				continue;
+			}
+		}
+
+		datum = heap_getattr(policy_tuple, Anum_pg_policy_polwithcheck,
+							 RelationGetDescr(pg_policy), &isnull);
+		if (!isnull)
+		{
+			str_value = TextDatumGetCString(datum);
+			expr = (Node *) stringToNode(str_value);
+			pfree(str_value);
+
+			pull_varattnos(expr, 1, &expr_attrs);
+			if (bms_is_member(0 - FirstLowInvalidHeapAttributeNumber, expr_attrs))
+			{
+				object.classId = PolicyRelationId;
+				object.objectId = policy->oid;
+				object.objectSubId = 0;
+
+				recordDependencyOn(&object, &tmpobject, DEPENDENCY_NORMAL);
+			}
+		}
+	}
+	systable_endscan(sscan);
+	table_close(pg_policy, AccessShareLock);
+	CommandCounterIncrement();
+
 	/*
 	 * Propagate to children as appropriate.  Unlike most other ALTER
 	 * routines, we have to do this one level of recursion at a time; we can't
diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out
index 3590d3274f0..ce2fb02971f 100644
--- a/src/test/regress/expected/constraints.out
+++ b/src/test/regress/expected/constraints.out
@@ -254,6 +254,23 @@ ERROR:  system column "ctid" reference in check constraint is invalid
 LINE 3:       CHECK (NOT (is_capital AND ctid::text = 'sys_col_check...
                                          ^
 --
+-- Drop column also drop the associated Check constraints and whole-row referenced check constraint
+--
+CREATE TABLE DROP_COL_CHECK_TBL (
+    city text, state text, is_capital bool, altitude int,
+    CONSTRAINT cc CHECK (city is not null),
+    CONSTRAINT cc1 CHECK (DROP_COL_CHECK_TBL is not null));
+ALTER TABLE DROP_COL_CHECK_TBL DROP COLUMN city;
+\d DROP_COL_CHECK_TBL
+           Table "public.drop_col_check_tbl"
+   Column   |  Type   | Collation | Nullable | Default 
+------------+---------+-----------+----------+---------
+ state      | text    |           |          | 
+ is_capital | boolean |           |          | 
+ altitude   | integer |           |          | 
+
+DROP TABLE DROP_COL_CHECK_TBL;
+--
 -- Check inheritance of defaults and constraints
 --
 CREATE TABLE INSERT_CHILD (cx INT default 42,
diff --git a/src/test/regress/expected/indexing.out b/src/test/regress/expected/indexing.out
index 4d29fb85293..ec32543c1e4 100644
--- a/src/test/regress/expected/indexing.out
+++ b/src/test/regress/expected/indexing.out
@@ -654,6 +654,31 @@ alter table idxpart2 drop column c;
  b      | integer |           |          | 
 
 drop table idxpart, idxpart2;
+create table idxpart (a int, b int, c int);
+create index on idxpart(c);
+create index on idxpart((idxpart is not null));
+create index on idxpart(a) where idxpart is not null;
+\d idxpart
+              Table "public.idxpart"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ a      | integer |           |          | 
+ b      | integer |           |          | 
+ c      | integer |           |          | 
+Indexes:
+    "idxpart_a_idx" btree (a) WHERE idxpart.* IS NOT NULL
+    "idxpart_c_idx" btree (c)
+    "idxpart_expr_idx" btree ((idxpart.* IS NOT NULL))
+
+alter table idxpart drop column c;
+\d idxpart
+              Table "public.idxpart"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ a      | integer |           |          | 
+ b      | integer |           |          | 
+
+drop table idxpart;
 -- Verify that expression indexes inherit correctly
 create table idxpart (a int, b int) partition by range (a);
 create table idxpart1 (like idxpart);
diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out
index 8c879509313..febe6538e47 100644
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -26,6 +26,27 @@ GRANT regress_rls_group2 TO regress_rls_carol;
 CREATE SCHEMA regress_rls_schema;
 GRANT ALL ON SCHEMA regress_rls_schema to public;
 SET search_path = regress_rls_schema;
+--check drop column also drop whole-row references policy
+CREATE TABLE rls_tbl (a int, b int, c int, CONSTRAINT cc1 CHECK (rls_tbl IS NOT NULL));
+CREATE POLICY p1 ON rls_tbl USING (rls_tbl >= ROW(1,1,1));
+CREATE POLICY p2 ON rls_tbl USING (rls_tbl.b = 1);
+ALTER TABLE rls_tbl DROP COLUMN b;
+ERROR:  cannot drop column b of table rls_tbl because other objects depend on it
+DETAIL:  policy p1 on table rls_tbl depends on column b of table rls_tbl
+policy p2 on table rls_tbl depends on column b of table rls_tbl
+HINT:  Use DROP ... CASCADE to drop the dependent objects too.
+ALTER TABLE rls_tbl DROP COLUMN b CASCADE;
+NOTICE:  drop cascades to 2 other objects
+DETAIL:  drop cascades to policy p1 on table rls_tbl
+drop cascades to policy p2 on table rls_tbl
+\d rls_tbl
+        Table "regress_rls_schema.rls_tbl"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ a      | integer |           |          | 
+ c      | integer |           |          | 
+
+DROP TABLE rls_tbl;
 -- setup of malicious function
 CREATE OR REPLACE FUNCTION f_leak(text) RETURNS bool
     COST 0.0000001 LANGUAGE plpgsql
diff --git a/src/test/regress/sql/constraints.sql b/src/test/regress/sql/constraints.sql
index 1f6dc8fd69f..545f8fa17a3 100644
--- a/src/test/regress/sql/constraints.sql
+++ b/src/test/regress/sql/constraints.sql
@@ -165,6 +165,17 @@ CREATE TABLE SYS_COL_CHECK_TBL (city text, state text, is_capital bool,
                   altitude int,
 				  CHECK (NOT (is_capital AND ctid::text = 'sys_col_check_tbl')));
 
+--
 
+create table idxpart (a int, b int, c int);
+create index on idxpart(c);
+create index on idxpart((idxpart is not null));
+create index on idxpart(a) where idxpart is not null;
+\d idxpart
+alter table idxpart drop column c;
+\d idxpart
+drop table idxpart;
+
 -- Verify that expression indexes inherit correctly
 create table idxpart (a int, b int) partition by range (a);
 create table idxpart1 (like idxpart);
diff --git a/src/test/regress/sql/rowsecurity.sql b/src/test/regress/sql/rowsecurity.sql
index 21ac0ca51ee..21784f86246 100644
--- a/src/test/regress/sql/rowsecurity.sql
+++ b/src/test/regress/sql/rowsecurity.sql
@@ -35,6 +35,15 @@ CREATE SCHEMA regress_rls_schema;
 GRANT ALL ON SCHEMA regress_rls_schema to public;
 SET search_path = regress_rls_schema;
 
+--check drop column also drop whole-row references policy
+CREATE TABLE rls_tbl (a int, b int, c int, CONSTRAINT cc1 CHECK (rls_tbl IS NOT NULL));
+CREATE POLICY p1 ON rls_tbl USING (rls_tbl >= ROW(1,1,1));
+CREATE POLICY p2 ON rls_tbl USING (rls_tbl.b = 1);
+ALTER TABLE rls_tbl DROP COLUMN b;
+ALTER TABLE rls_tbl DROP COLUMN b CASCADE;
+\d rls_tbl
+DROP TABLE rls_tbl;
+
 -- setup of malicious function
 CREATE OR REPLACE FUNCTION f_leak(text) RETURNS bool
     COST 0.0000001 LANGUAGE plpgsql
-- 
2.34.1

---

From: Chao Li <[email-masked]>
Date: Thu, 11 Sep 2025 09:27:27 +0800

> On Sep 9, 2025, at 11:12, jian he <[email-masked]> wrote:
> 
> hi.
> <v2-0001-ALTER-TABLE-DROP-COLUMN-drop-wholerow-referenced-object.patch>

I agree we should delete those constraints and indices on the whole row, otherwise, with cc (ts=ROW(1,1,1)), once a column is dropped, it won’t be able to insert data anymore:

```
evantest=# insert into ts values (2, 3);
ERROR:  new row for relation "ts" violates check constraint "cc"
DETAIL:  Failing row contains (2, 3).
evantest=# insert into ts values (1, 1);
ERROR:  cannot compare record types with different numbers of columns
```

But v2 needs a rebase, I cannot apply it to master.

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

---

From: jian he <[email-masked]>
Date: Fri, 12 Sep 2025 17:22:37 +0800

> But v2 needs a rebase, I cannot apply it to master.

hi.
please check attached v3-0001, v3-0002.

v3-0001: ALTER TABLE DROP COLUMN cascade to drop any whole-row
referenced constraints and indexes.
v3-0002: ALTER COLUMN SET DATA TYPE error out when whole-row
referenced constraint exists

with the v3-0002 patch,
CREATE TABLE ts (a int, c int, b int  constraint cc check((ts = ROW(1,1,1))));
ALTER TABLE ts ALTER COLUMN b SET DATA TYPE INT8;
ERROR:  cannot alter table column "ts"."b" to type bigint because
constraint "cc" uses table "ts" row type
HINT:  You might need to drop constraint "cc" first to change column
"ts"."b" data type

Of course, even if we do not error out, regular insert will fail too.
insert into ts values(1,1,1);
ERROR:  cannot compare dissimilar column types bigint and integer at
record column 3
src7=# \errverbose
ERROR:  42804: cannot compare dissimilar column types bigint and
integer at record column 3
LOCATION:  record_eq, rowtypes.c:1193
then you need debug to find out the root error cause is constraint cc
is not being satisfied;
and you still need to handle the corrupted constraint cc afterward.
With the v3-0002 patch, ALTER TABLE SET DATA TYPE provides an explicit error
message that helps quickly identify the problem.
So I guess it should be helpful.

--------------------------------
index expression/predicate and check constraint expression can not contain
subquery, that's why using pull_varattnos to test whole-row containment works
fine.  but pull_varattnos can not cope with subquery, see pull_varattnos
comments.

row security policy can have subquery, for example:
CREATE POLICY p1 ON document AS PERMISSIVE
USING (dlevel <= (SELECT seclv FROM uaccount WHERE pguser = current_user));

so I am still working on whole-row referenced policies interacting with ALTER
TABLE SET DATA TYPE/ALTER TABLE DROP COLUMN.
From 090a087da9b7fb072acd7e9683faf9ba2b5c76af Mon Sep 17 00:00:00 2001
From: jian he <[email-masked]>
Date: Fri, 12 Sep 2025 17:10:19 +0800
Subject: [PATCH v3 2/2] disallow ALTER COLUMN SET DATA TYPE when wholerow
 referenced constraint exists

CREATE TABLE ts (a int, c int, b int  constraint cc check((ts = ROW(1,1,1))));
INSERT INTO ts values (1,1,1);

ALTER TABLE ts ALTER COLUMN b SET DATA TYPE INT8;
ERROR:  cannot alter table column "ts"."b" to type bigint because constraint "cc" uses table "ts" row type
HINT:  You might need to drop constraint "cc" first to change column "ts"."b" data type

No need to worry about the index; index rebuild will fail automatically.
discussion: https://postgr.es/m/[email-masked]
---
 src/backend/commands/tablecmds.c          | 40 +++++++++++++++++++++++
 src/test/regress/expected/constraints.out | 11 +++++++
 src/test/regress/sql/constraints.sql      | 10 ++++++
 3 files changed, 61 insertions(+)

diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 409bc65e53f..62c84726c4e 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -14664,6 +14664,46 @@ ATPrepAlterColumnType(List **wqueue,
 		find_composite_type_dependencies(rel->rd_rel->reltype, rel, NULL);
 	}
 
+	/*
+	 * If the table has a whole-row referenced CHECK constraint, then changing
+	 * any column data type is not allowed.
+	*/
+	if (targettype != attTup->atttypid || targettypmod != attTup->atttypmod)
+	{
+		TupleConstr *constr = RelationGetDescr(rel)->constr;
+
+		if (constr && constr->num_check > 0)
+		{
+			ConstrCheck *check = constr->check;
+			Node	*check_expr;
+
+			for (int i = 0; i < constr->num_check; i++)
+			{
+				Bitmapset  *expr_attrs = NULL;
+
+				char	   *constr_name = check[i].ccname;
+				check_expr = stringToNode(check[i].ccbin);
+
+				/* Find all attributes referenced */
+				pull_varattnos(check_expr, 1, &expr_attrs);
+
+				if (bms_is_member(0 - FirstLowInvalidHeapAttributeNumber, expr_attrs))
+					ereport(ERROR,
+							errcode(ERRCODE_DATATYPE_MISMATCH),
+							errmsg("cannot alter table column \"%s\".\"%s\" to type %s because constraint \"%s\" uses table \"%s\" row type",
+								   RelationGetRelationName(rel),
+								   colName,
+								   format_type_with_typemod(targettype, targettypmod),
+								   constr_name,
+								   RelationGetRelationName(rel)),
+							errhint("You might need to drop constraint \"%s\" first to change column \"%s\".\"%s\" data type",
+									constr_name,
+									RelationGetRelationName(rel),
+									colName));
+			}
+		}
+	}
+
 	ReleaseSysCache(tuple);
 
 	/*
diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out
index ce2fb02971f..6de0cc4ec33 100644
--- a/src/test/regress/expected/constraints.out
+++ b/src/test/regress/expected/constraints.out
@@ -271,6 +271,17 @@ ALTER TABLE DROP_COL_CHECK_TBL DROP COLUMN city;
 
 DROP TABLE DROP_COL_CHECK_TBL;
 --
+-- Change column data type should error out because the whole-row referenced
+-- check constraint is still need it.
+--
+CREATE TABLE ALTER_COL_CHECK_TBL (
+    city text, state text, is_capital bool, altitude int,
+    CONSTRAINT cc1 CHECK (ALTER_COL_CHECK_TBL is not null));
+ALTER TABLE ALTER_COL_CHECK_TBL ALTER COLUMN city SET DATA TYPE name;
+ERROR:  cannot alter table column "alter_col_check_tbl"."city" to type name because constraint "cc1" uses table "alter_col_check_tbl" row type
+HINT:  You might need to drop constraint "cc1" first to change column "alter_col_check_tbl"."city" data type
+DROP TABLE ALTER_COL_CHECK_TBL;
+--
 -- Check inheritance of defaults and constraints
 --
 CREATE TABLE INSERT_CHILD (cx INT default 42,
diff --git a/src/test/regress/sql/constraints.sql b/src/test/regress/sql/constraints.sql
index 545f8fa17a3..3d33d3e2d9a 100644
--- a/src/test/regress/sql/constraints.sql
+++ b/src/test/regress/sql/constraints.sql
@@ -176,6 +176,16 @@ ALTER TABLE DROP_COL_CHECK_TBL DROP COLUMN city;
 \d DROP_COL_CHECK_TBL
 DROP TABLE DROP_COL_CHECK_TBL;
 
+--
+-- Change column data type should error out because the whole-row referenced
+-- check constraint is still need it.
+--
+CREATE TABLE ALTER_COL_CHECK_TBL (
+    city text, state text, is_capital bool, altitude int,
+    CONSTRAINT cc1 CHECK (ALTER_COL_CHECK_TBL is not null));
+ALTER TABLE ALTER_COL_CHECK_TBL ALTER COLUMN city SET DATA TYPE name;
+DROP TABLE ALTER_COL_CHECK_TBL;
+
 --
 -- Check inheritance of defaults and constraints
 --
-- 
2.34.1

From 5d0eb5f8091c666fe20eefa7b1fbd7666c966b55 Mon Sep 17 00:00:00 2001
From: jian he <[email-masked]>
Date: Fri, 12 Sep 2025 16:30:18 +0800
Subject: [PATCH v3 1/2] ALTER TABLE DROP COLUMN drop wholerow referenced
 object

ALTER TABLE ts DROP COLUMN a CASCADE;
will drop above all indexes, constraints on the table ts.

now
\d ts
                 Table "public.ts"

discussion: https://postgr.es/m/[email-masked]
---
 src/backend/commands/tablecmds.c          | 141 ++++++++++++++++++++++
 src/test/regress/expected/constraints.out |  17 +++
 src/test/regress/expected/indexing.out    |  13 ++
 src/test/regress/sql/constraints.sql      |  11 ++
 src/test/regress/sql/indexing.sql         |   8 ++
 5 files changed, 190 insertions(+)

diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 3be2e051d32..409bc65e53f 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -9260,7 +9260,11 @@ ATExecDropColumn(List **wqueue, Relation rel, const char *colName,
 	AttrNumber	attnum;
 	List	   *children;
 	ObjectAddress object;
+	ObjectAddress tmpobject;
 	bool		is_expr;
+	Node	   *expr;
+	List	   *indexlist = NIL;
+	TupleConstr *constr = RelationGetDescr(rel)->constr;
 
 	/* At top level, permission check was done in ATPrepCmd, else do it */
 	if (recursing)
@@ -9333,6 +9337,143 @@ ATExecDropColumn(List **wqueue, Relation rel, const char *colName,
 
 	ReleaseSysCache(tuple);
 
index 4d29fb85293..cac1cca3a1f 100644
@@ -654,6 +654,19 @@ alter table idxpart2 drop column c;
  b      | integer |           |          | 

+--
index b5cb01c2d70..2bad9555112 100644
@@ -295,6 +295,14 @@ alter table idxpart2 drop column c;

---

From: Chao Li <[email-masked]>
Date: Mon, 15 Sep 2025 11:47:57 +0800

> <v3-0002-disallow-ALTER-COLUMN-SET-DATA-TYPE-when-wholerow-referenced-cons.patch><v3-0001-ALTER-TABLE-DROP-COLUMN-drop-wholerow-referenced-object.patch>

1 - 0001
```

Nit: need -> needs to

2 - 0001
```

Originally “object” points to the column to delete, but with is patch, you are using “object” for index/constrain to delete and “tmpobject” for the column to delete, which could be misleading.

I’d suggest keep the meaning of “object” unchanged, you need to pull up of initialization of “object” to the place now you initiate “tmpobject”. And only define “tmpobject” in sections where it is needed. So you can name it “idxObject” or “consObject”, which will be more clearly meaning. I think you may also rename “object” to “colObject”.

3 - 0001
```
+			}
+		}

Why CommandCounterIncrement() is needed? In current code, there is a CommandCounterIncrement() after CatalogTupleUpdate(), which is necessary. But for your new code, maybe you considered “recordDependencyOn()” needs CommandCounterIncrement(). I searched over all places when “recordDependencyOn()” is called, I don’t see CommandCounterIncrement() is called.

4 - 0001
```
                   ….
                   …
+		}
```

These two pieces of code are exactly the same expect operating different Anum_pg_index_indpred/indexprs. I think we can create a static function to avoid duplicate code.

5 - 0001
···
···

Should we continue after elog()?

6 - 0002
```
+					ereport(ERROR,

I think the second relation name is quite duplicate. We can just say “because constraint “xx” uses whole-row type". 

Best regards,

---

From: jian he <[email-masked]>
Date: Mon, 15 Sep 2025 20:40:36 +0800

> Why CommandCounterIncrement() is needed? In current code, there is a CommandCounterIncrement() after CatalogTupleUpdate(), which is necessary. But for your new code, maybe you considered “recordDep…
My thought is that CommandCounterIncrement may be needed;
because recordDependencyOn inserts many tuples to pg_depend,
then later performMultipleDeletions will interact with pg_depend.

> Should we continue after elog()?
if "elog(ERROR," happens, then it will abort, so there is no need to
"continue", I think.

Summary of attached v4:
v4-0001: Handles ALTER TABLE DROP COLUMN when whole-row Vars are
referenced in check constraints and indexes.

v4-0002: Handles ALTER TABLE ALTER COLUMN SET DATA TYPE when whole-row
Vars are referenced in check constraints and indexes.

v4-0003: Handle ALTER TABLE ALTER COLUMN SET DATA TYPE and ALTER TABLE DROP
COLUMN when policy objects reference whole-row Vars.  Policy quals and check
quals may contain whole-row Vars and can include sublinks (unplanned
subqueries), pull_varattnos is not enough to locate whole-row Var. Instead,
obtain the whole-row type OID and recursively check each Var in expression node
to see if its vartype matches the whole-row type OID.
From 5d3ad72059977c6c3576c92e1ba25684f80b628d Mon Sep 17 00:00:00 2001
From: jian he <[email-masked]>
Date: Mon, 15 Sep 2025 20:19:29 +0800
Subject: [PATCH v4 3/3] disallow change or drop column when wholerow
 referenced policy exists

demo:
CREATE TABLE rls_tbl (a int, b int, c int);
CREATE POLICY p1 ON rls_tbl USING (rls_tbl >= ROW(1,1,1));

ALTER TABLE rls_tbl DROP COLUMN b; --error
ERROR:  cannot drop column b of table rls_tbl because other objects depend on it
DETAIL:  policy p1 on table rls_tbl depends on column b of table rls_tbl
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

ALTER TABLE rls_tbl ALTER COLUMN b SET DATA TYPE BIGINT; --error
ERROR:  cannot alter table "rls_tbl" because security policy "p1" uses its row type
HINT:  You might need to drop policy "p1" first

ALTER TABLE rls_tbl DROP COLUMN b CASCADE; --ok
NOTICE:  drop cascades to policy p1 on table rls_tbl
ALTER TABLE

discussion: https://postgr.es/m/[email-masked]
---
 src/backend/commands/tablecmds.c          | 91 ++++++++++++++++++++++-
 src/backend/optimizer/util/var.c          | 60 +++++++++++++++
 src/include/optimizer/optimizer.h         |  1 +
 src/test/regress/expected/rowsecurity.out | 17 +++++
 src/test/regress/sql/rowsecurity.sql      | 12 +++
 src/tools/pgindent/typedefs.list          |  1 +
 6 files changed, 181 insertions(+), 1 deletion(-)

diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 4949d7204a0..403b8a1adb8 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -22096,7 +22096,7 @@ GetAttributeStorage(Oid atttypid, const char *storagemode)
 }
 
 /*
- * Record dependencies between whole-row objects (indexes, constraints)
+ * Record dependencies between whole-row objects (indexes, constraints or policies)
  * associated with relation and relation's ObjectAddress.
  * error_out means can not install such dependency, we have to error out explicitly.
  */
@@ -22107,9 +22107,15 @@ recordWholeRowDependencyOnOrError(Relation rel, const ObjectAddress *object, boo
 	List	   *indexlist = NIL;
 	ObjectAddress con_obj;
 	ObjectAddress idx_obj;
+	ObjectAddress pol_obj;
 	bool		find_wholerow = false;
 
 	TupleConstr *constr = RelationGetDescr(rel)->constr;
+	Relation	pg_policy;
+	ScanKeyData skey[1];
+	SysScanDesc sscan;
+	HeapTuple	policy_tuple;
+	Oid			reltypid;
 
 	if (constr && constr->num_check > 0)
 	{
@@ -22265,4 +22271,87 @@ recordWholeRowDependencyOnOrError(Relation rel, const ObjectAddress *object, boo
 		}
 		ReleaseSysCache(indexTuple);
 	}
+
+	find_wholerow = false;
+	reltypid = get_rel_type_id(RelationGetRelid(rel));
+
+	pg_policy = table_open(PolicyRelationId, AccessShareLock);
+	ScanKeyInit(&skey[0],
+				Anum_pg_policy_polrelid,
+				BTEqualStrategyNumber, F_OIDEQ,
+				ObjectIdGetDatum(RelationGetRelid(rel)));
+	sscan = systable_beginscan(pg_policy,
+							   PolicyPolrelidPolnameIndexId, true, NULL, 1,
+							   skey);
+	while (HeapTupleIsValid(policy_tuple = systable_getnext(sscan)))
+	{
+		Datum		datum;
+		bool		isnull;
+		char	   *str_value;
+		Node		*polexpr;
+
+		Form_pg_policy policy = (Form_pg_policy) GETSTRUCT(policy_tuple);
+
+		/* Get policy qual */
+		datum = heap_getattr(policy_tuple, Anum_pg_policy_polqual,
+							 RelationGetDescr(pg_policy), &isnull);
+		if (!isnull)
+		{
+			str_value = TextDatumGetCString(datum);
+			polexpr = (Node *) stringToNode(str_value);
+			pfree(str_value);
+
+			find_wholerow = ExprContainWholeRow(polexpr, reltypid);
+			if (find_wholerow && !error_out)
+			{
+				pol_obj.classId = PolicyRelationId;
+				pol_obj.objectId = policy->oid;
+				pol_obj.objectSubId = 0;
+
+				/* record dependency for policies that references whole-row Var */
+				recordDependencyOn(&pol_obj, object, DEPENDENCY_NORMAL);
+				continue;
+			}
+
+			if (find_wholerow && error_out)
+				ereport(ERROR,
+						errcode(ERRCODE_DATATYPE_MISMATCH),
+						errmsg("cannot alter table \"%s\" because security policy \"%s\" uses its row type",
+								RelationGetRelationName(rel),
+								NameStr(policy->polname)),
+						errhint("You might need to drop policy \"%s\" first",
+								NameStr(policy->polname)));
+		}
+
+		datum = heap_getattr(policy_tuple, Anum_pg_policy_polwithcheck,
+							 RelationGetDescr(pg_policy), &isnull);
+		if (!isnull)
+		{
+			str_value = TextDatumGetCString(datum);
+			polexpr = (Node *) stringToNode(str_value);
+			pfree(str_value);
+
+			find_wholerow = ExprContainWholeRow(polexpr, reltypid);
+			if (find_wholerow && !error_out)
+			{
+				pol_obj.classId = PolicyRelationId;
+				pol_obj.objectId = policy->oid;
+				pol_obj.objectSubId = 0;
+
+				/* record dependency for policies that references whole-row Var */
+				recordDependencyOn(&pol_obj, object, DEPENDENCY_NORMAL);
+			}
+
+			if (find_wholerow && error_out)
+				ereport(ERROR,
+						errcode(ERRCODE_DATATYPE_MISMATCH),
+						errmsg("cannot alter table \"%s\" because security policy \"%s\" uses its row type",
+								RelationGetRelationName(rel),
+								NameStr(policy->polname)),
+						errhint("You might need to drop policy \"%s\" first",
+								NameStr(policy->polname)));
+		}
+	}
+	systable_endscan(sscan);
+	table_close(pg_policy, AccessShareLock);
 }
diff --git a/src/backend/optimizer/util/var.c b/src/backend/optimizer/util/var.c
index 8065237a189..36de0a3bfca 100644
--- a/src/backend/optimizer/util/var.c
+++ b/src/backend/optimizer/util/var.c
@@ -48,6 +48,10 @@ typedef struct
 	List	   *vars;
 	int			sublevels_up;
 } pull_vars_context;
+typedef struct
+{
+	Oid			reltypid;
+} contain_wholerow_context;
 
 typedef struct
 {
@@ -73,6 +77,7 @@ typedef struct
 static bool pull_varnos_walker(Node *node,
 							   pull_varnos_context *context);
 static bool pull_varattnos_walker(Node *node, pull_varattnos_context *context);
+static bool ExprContainWholeRow_walker(Node *node, contain_wholerow_context *context);
 static bool pull_vars_walker(Node *node, pull_vars_context *context);
 static bool contain_var_clause_walker(Node *node, void *context);
 static bool contain_vars_of_level_walker(Node *node, int *sublevels_up);
@@ -327,6 +332,61 @@ pull_varattnos_walker(Node *node, pull_varattnos_context *context)
 	return expression_tree_walker(node, pull_varattnos_walker, context);
 }
 
+static bool
+ExprContainWholeRow_walker(Node *node, contain_wholerow_context *context)
+{
+	if (node == NULL)
+		return false;
+
+	if (IsA(node, Var))
+	{
+		Var		   *var = (Var *) node;
+
+		if (var->varattno == InvalidAttrNumber &&
+			var->vartype == context->reltypid)
+			return true;
+
+		return false;
+	}
+
+	if (IsA(node, Query))
+	{
+		/* Recurse into RTE subquery or not-yet-planned sublink subquery */
+		bool		result;
+
+		result = query_tree_walker((Query *) node, ExprContainWholeRow_walker,
+								   context, 0);
+		return result;
+	}
+
+	return expression_tree_walker(node, ExprContainWholeRow_walker, context);
+}
+
+
+/*
+ * ExprContainWholeRow -
+ *
+ * Determine whether an expression contains a whole-row Var, recursing as needed.
+ * For simple expressions without sublinks, pull_varattnos is usually sufficient
+ * to detect a whole-row Var. If the node contains sublinks (unplanned subqueries),
+ * the check must instead rely on the whole-row type OID. Therefore, reltypid is
+ * used consistently to determine the presence of a whole-row Var.
+ */
+bool
+ExprContainWholeRow(Node *node, Oid reltypid)
+{
+	contain_wholerow_context context;
+
+	context.reltypid = reltypid;
+
+	Assert(OidIsValid(reltypid));
+
+	return query_or_expression_tree_walker(node,
+										   ExprContainWholeRow_walker,
+										   &context,
+										   0);
+}
+
 
 /*
  * pull_vars_of_level
diff --git a/src/include/optimizer/optimizer.h b/src/include/optimizer/optimizer.h
index 37bc13c2cbd..d31d1c49c75 100644
--- a/src/include/optimizer/optimizer.h
+++ b/src/include/optimizer/optimizer.h
@@ -205,6 +205,7 @@ extern SortGroupClause *get_sortgroupref_clause_noerr(Index sortref,
 extern Bitmapset *pull_varnos(PlannerInfo *root, Node *node);
 extern Bitmapset *pull_varnos_of_level(PlannerInfo *root, Node *node, int levelsup);
 extern void pull_varattnos(Node *node, Index varno, Bitmapset **varattnos);
+extern bool ExprContainWholeRow(Node *node, Oid reltypid);
 extern List *pull_vars_of_level(Node *node, int levelsup);
 extern bool contain_var_clause(Node *node);
 extern bool contain_vars_of_level(Node *node, int levelsup);
diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out
index 8c879509313..b2b5333f8dc 100644
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -2357,6 +2357,23 @@ SELECT * FROM document;
   14 |  11 |      1 | regress_rls_bob   | new novel                        | 
 (16 rows)
 
+--check drop column restrict or alter column data type will fail because of whole-row
+--referenced security policy exists.
+ALTER TABLE document ADD COLUMN dummy INT4;
+CREATE POLICY p7 ON document AS PERMISSIVE
+    USING (cid IS NOT NULL AND
+    (WITH cte AS (SELECT TRUE FROM uaccount
+        WHERE EXISTS (SELECT document FROM uaccount WHERE uaccount IS NULL))
+    SELECT * FROM cte));
+ALTER TABLE document ALTER COLUMN dummy SET DATA TYPE BIGINT; --error
+ERROR:  cannot alter table "document" because security policy "p7" uses its row type
+HINT:  You might need to drop policy "p7" first
+ALTER TABLE document DROP COLUMN dummy; --error
+ERROR:  cannot drop column dummy of table document because other objects depend on it
+DETAIL:  policy p7 on table document depends on column dummy of table document
+HINT:  Use DROP ... CASCADE to drop the dependent objects too.
+ALTER TABLE document DROP COLUMN dummy CASCADE; --ok
+NOTICE:  drop cascades to policy p7 on table document
 --
 -- ROLE/GROUP
 --
diff --git a/src/test/regress/sql/rowsecurity.sql b/src/test/regress/sql/rowsecurity.sql
index 21ac0ca51ee..0cdd5a82ca3 100644
--- a/src/test/regress/sql/rowsecurity.sql
+++ b/src/test/regress/sql/rowsecurity.sql
@@ -1021,6 +1021,18 @@ DROP POLICY p1 ON document;
 -- Just check everything went per plan
 SELECT * FROM document;
 
+--check drop column restrict or alter column data type will fail because of whole-row
+--referenced security policy exists.
+ALTER TABLE document ADD COLUMN dummy INT4;
+CREATE POLICY p7 ON document AS PERMISSIVE
+    USING (cid IS NOT NULL AND
+    (WITH cte AS (SELECT TRUE FROM uaccount
+        WHERE EXISTS (SELECT document FROM uaccount WHERE uaccount IS NULL))
+    SELECT * FROM cte));
+ALTER TABLE document ALTER COLUMN dummy SET DATA TYPE BIGINT; --error
+ALTER TABLE document DROP COLUMN dummy; --error
+ALTER TABLE document DROP COLUMN dummy CASCADE; --ok
+
 --
 -- ROLE/GROUP
 --
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index a13e8162890..81b97cc622e 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -3521,6 +3521,7 @@ conn_oauth_scope_func
 conn_sasl_state_func
 contain_aggs_of_level_context
 contain_placeholder_references_context
+contain_wholerow_context
 convert_testexpr_context
 copy_data_dest_cb
 copy_data_source_cb
-- 
2.34.1

From edf09b4d7da937d946f5c6ef6ca6806019d37fe0 Mon Sep 17 00:00:00 2001
From: jian he <[email-masked]>
Date: Mon, 15 Sep 2025 15:32:49 +0800
Subject: [PATCH v4 1/3] ALTER TABLE DROP COLUMN drop wholerow referenced
 object

now
\d ts
                 Table "public.ts"

discussion: https://postgr.es/m/[email-masked]
---
 src/backend/commands/tablecmds.c          | 170 +++++++++++++++++++++-
 src/test/regress/expected/constraints.out |  17 +++
 src/test/regress/expected/indexing.out    |  13 ++
 src/test/regress/sql/constraints.sql      |  11 ++
 src/test/regress/sql/indexing.sql         |   8 +
 5 files changed, 216 insertions(+), 3 deletions(-)

diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 3be2e051d32..7f5e58da62f 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -740,6 +740,7 @@ static List *GetParentedForeignKeyRefs(Relation partition);
 static void ATDetachCheckNoForeignKeyRefs(Relation partition);
 static char GetAttributeCompression(Oid atttypid, const char *compression);
 static char GetAttributeStorage(Oid atttypid, const char *storagemode);
+static void recordWholeRowDependencyOnOrError(Relation rel, const ObjectAddress *object, bool error_out);

 /* ----------------------------------------------------------------
@@ -9333,6 +9334,26 @@ ATExecDropColumn(List **wqueue, Relation rel, const char *colName,
 
 	ReleaseSysCache(tuple);
 
+	object.classId = RelationRelationId;
+	object.objectId = RelationGetRelid(rel);
+	object.objectSubId = attnum;
+
+	/*
+	 * ALTER TABLE DROP COLUMN must also remove indexes or constraints that
+	 * contain whole-row Var reference expressions. Since there is no direct
+	 * dependency recorded between whole-row Vars and individual columns, and
+	 * creating such dependencies would cause catalog bloat (see
+	 * find_expr_references_walker).
+	 *
+	 * Here we handle this explicitly. We call recordWholeRowDependencyOnOrError
+	 * to establish a dependency between the column and any constraint or index
+	 * involving whole-row Vars.  performMultipleDeletions will then take care
+	 * of removing them.
+	*/
+	recordWholeRowDependencyOnOrError(rel, &object, false);
+
+	CommandCounterIncrement();
+
 	/*
 	 * Propagate to children as appropriate.  Unlike most other ALTER
 	 * routines, we have to do this one level of recursion at a time; we can't
@@ -9426,9 +9447,6 @@ ATExecDropColumn(List **wqueue, Relation rel, const char *colName,
 	}
 
 	/* Add object to delete */
-	object.classId = RelationRelationId;
-	object.objectId = RelationGetRelid(rel);
-	object.objectSubId = attnum;
 	add_exact_object_address(&object, addrs);
 
 	if (!recursing)
@@ -22062,3 +22080,149 @@ GetAttributeStorage(Oid atttypid, const char *storagemode)
 
 	return cstorage;
 }
+
+/*
+ * Record dependencies between whole-row objects (indexes, constraints)
+ * associated with relation and relation's ObjectAddress.
+ * error_out means can not install such dependency, we have to error out explicitly.
+ */
+static void
+recordWholeRowDependencyOnOrError(Relation rel, const ObjectAddress *object, bool error_out)
+{
+	Node	   *expr;
+	List	   *indexlist = NIL;
+	ObjectAddress con_obj;
+	ObjectAddress idx_obj;
+	bool		find_wholerow = false;
+
+	TupleConstr *constr = RelationGetDescr(rel)->constr;
+
+	if (constr && constr->num_check > 0)
+	{
+		ConstrCheck *check = constr->check;
+
+		for (int i = 0; i < constr->num_check; i++)
+		{
+			Bitmapset  *expr_attrs = NULL;
+			char	   *constr_name = check[i].ccname;
+
+			expr = stringToNode(check[i].ccbin);
+
+			/* Find all attributes referenced */
+			pull_varattnos(expr, 1, &expr_attrs);
+
+			find_wholerow = bms_is_member(0 - FirstLowInvalidHeapAttributeNumber,
+										  expr_attrs);
+			if (find_wholerow && !error_out)
+			{
+				Relation	conDesc;
+				SysScanDesc conscan;
+				ScanKeyData skey[3];
+				HeapTuple	contuple;
+
+				/* Search for a pg_constraint entry with same name and relation */
+				conDesc = table_open(ConstraintRelationId, AccessShareLock);
+
+				ScanKeyInit(&skey[0],
+							Anum_pg_constraint_conrelid,
+							BTEqualStrategyNumber, F_OIDEQ,
+							ObjectIdGetDatum(RelationGetRelid(rel)));
+				ScanKeyInit(&skey[1],
+							Anum_pg_constraint_contypid,
+							BTEqualStrategyNumber, F_OIDEQ,
+							ObjectIdGetDatum(InvalidOid));
+				ScanKeyInit(&skey[2],
+							Anum_pg_constraint_conname,
+							BTEqualStrategyNumber, F_NAMEEQ,
+							CStringGetDatum(constr_name));
+
+				conscan = systable_beginscan(conDesc, ConstraintRelidTypidNameIndexId, true,
+											 NULL, 3, skey);
+				if (!HeapTupleIsValid(contuple = systable_getnext(conscan)))
+					elog(ERROR, "constraint \"%s\" of relation \"%s\" does not exist",
+						 constr_name, RelationGetRelationName(rel));
+
+				con_obj.classId = ConstraintRelationId;
+				con_obj.objectId = ((Form_pg_constraint) GETSTRUCT(contuple))->oid;
+				con_obj.objectSubId = 0;
+
+				/* record dependency for constraints that references whole-row */
+				recordDependencyOn(&con_obj, object, DEPENDENCY_AUTO);
+
+				systable_endscan(conscan);
+				table_close(conDesc, AccessShareLock);
+			}
+		}
+	}
+
+	find_wholerow = false;
+	indexlist = RelationGetIndexList(rel);
+	foreach_oid(indexoid, indexlist)
+	{
+		HeapTuple	indexTuple;
+		Form_pg_index indexStruct;
+		Node	   *node;
+
+		indexTuple = SearchSysCache1(INDEXRELID, ObjectIdGetDatum(indexoid));
+		if (!HeapTupleIsValid(indexTuple))
+			elog(ERROR, "cache lookup failed for index %u", indexoid);
+		indexStruct = (Form_pg_index) GETSTRUCT(indexTuple);
+
+		if (!heap_attisnull(indexTuple, Anum_pg_index_indpred, NULL))
+		{
+			Datum		predDatum;
+			char	   *predString;
+			Bitmapset  *expr_attrs = NULL;
+
+			/* Convert text string to node tree */
+			predDatum = SysCacheGetAttrNotNull(INDEXRELID, indexTuple,
+											   Anum_pg_index_indpred);
+			predString = TextDatumGetCString(predDatum);
+			node = (Node *) stringToNode(predString);
+			pfree(predString);
+
+			pull_varattnos(node, 1, &expr_attrs);
+			find_wholerow = bms_is_member(0 - FirstLowInvalidHeapAttributeNumber,
+										  expr_attrs);
+			if (find_wholerow && !error_out)
+			{
+				idx_obj.classId = RelationRelationId;
+				idx_obj.objectId = indexStruct->indexrelid;
+				idx_obj.objectSubId = 0;
+				/* record dependency for indexes that references whole-row */
+				recordDependencyOn(&idx_obj, object, DEPENDENCY_AUTO);
+
+				ReleaseSysCache(indexTuple);
+				continue;
+			}
+		}
+
+		if (!heap_attisnull(indexTuple, Anum_pg_index_indexprs, NULL))
+		{
+			Datum		exprDatum;
+			char	   *exprString;
+			Bitmapset  *expr_attrs = NULL;
+
+			/* Convert text string to node tree */
+			exprDatum = SysCacheGetAttrNotNull(INDEXRELID, indexTuple,
+											   Anum_pg_index_indexprs);
+			exprString = TextDatumGetCString(exprDatum);
+			node = (Node *) stringToNode(exprString);
+			pfree(exprString);
+
+			pull_varattnos(node, 1, &expr_attrs);
+
+			find_wholerow = bms_is_member(0 - FirstLowInvalidHeapAttributeNumber,
+										  expr_attrs);
+			if (find_wholerow && !error_out)
+			{
+				idx_obj.classId = RelationRelationId;
+				idx_obj.objectId = indexStruct->indexrelid;
+				idx_obj.objectSubId = 0;
+				/* record dependency for indexes that references whole-row */
+				recordDependencyOn(&idx_obj, object, DEPENDENCY_AUTO);
+			}
+		}
+		ReleaseSysCache(indexTuple);
+	}
+}
diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out
index 3590d3274f0..8930b4da7ce 100644
--- a/src/test/regress/expected/constraints.out
+++ b/src/test/regress/expected/constraints.out
@@ -254,6 +254,23 @@ ERROR:  system column "ctid" reference in check constraint is invalid
 LINE 3:       CHECK (NOT (is_capital AND ctid::text = 'sys_col_check...
                                          ^
 --
+-- Drop column also drop check constraints that have whole-row reference
+--
+CREATE TABLE DROP_COL_CHECK_TBL (
+    city text, state text, is_capital bool, altitude int,
+    CONSTRAINT cc CHECK (city is not null),
+    CONSTRAINT cc1 CHECK (DROP_COL_CHECK_TBL is not null));
+ALTER TABLE DROP_COL_CHECK_TBL DROP COLUMN city;
+\d DROP_COL_CHECK_TBL
+           Table "public.drop_col_check_tbl"
+   Column   |  Type   | Collation | Nullable | Default 
+------------+---------+-----------+----------+---------
+ state      | text    |           |          | 
+ is_capital | boolean |           |          | 
+ altitude   | integer |           |          | 
+
+DROP TABLE DROP_COL_CHECK_TBL;
+--
 -- Check inheritance of defaults and constraints
 --
 CREATE TABLE INSERT_CHILD (cx INT default 42,
diff --git a/src/test/regress/expected/indexing.out b/src/test/regress/expected/indexing.out
index 4d29fb85293..cac1cca3a1f 100644
--- a/src/test/regress/expected/indexing.out
+++ b/src/test/regress/expected/indexing.out
@@ -654,6 +654,19 @@ alter table idxpart2 drop column c;
  b      | integer |           |          | 
 
index 1f6dc8fd69f..676d8736101 100644
 
+--
+-- Drop column also drop check constraints that have whole-row reference
+--

From 07ac16212729fb71b0f3fa8557688e0788477625 Mon Sep 17 00:00:00 2001
From: jian he <[email-masked]>
Date: Mon, 15 Sep 2025 15:53:59 +0800
Subject: [PATCH v4 2/3] disallow ALTER COLUMN SET DATA TYPE when wholerow
 referenced constraint exists

ALTER TABLE ts ALTER COLUMN b SET DATA TYPE INT8;
ERROR:  cannot alter table "ts" because constraint "cc" uses its row type
HINT:  You might need to drop constraint "cc" first

create index on ts((ts is not null));
alter table ts alter column a set data type int8; --error
ERROR:  cannot alter table "ts" because index "ts_expr_idx" uses its row type
HINT:  You might need to drop index "ts_expr_idx" first

discussion: https://postgr.es/m/[email-masked]
---
 src/backend/commands/tablecmds.c          | 40 +++++++++++++++++++++++
 src/test/regress/expected/constraints.out | 11 +++++++
 src/test/regress/expected/indexing.out    |  6 ++++
 src/test/regress/sql/constraints.sql      | 10 ++++++
 src/test/regress/sql/indexing.sql         |  2 ++
 5 files changed, 69 insertions(+)

diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 7f5e58da62f..4949d7204a0 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -14541,6 +14541,20 @@ ATPrepAlterColumnType(List **wqueue,
 		find_composite_type_dependencies(rel->rd_rel->reltype, rel, NULL);
 	}
 
+	/*
+	 * If the table has a whole-row referenced CHECK constraint, then changing
+	 * any column data type is not allowed.
+	*/
+	if (targettype != attTup->atttypid || targettypmod != attTup->atttypmod)
+	{
+		ObjectAddress object;
+
+		object.classId = RelationRelationId;
+		object.objectId = RelationGetRelid(rel);
+		object.objectSubId = attnum;
+		recordWholeRowDependencyOnOrError(rel, &object, true);
+	}
+
 	ReleaseSysCache(tuple);
 
 	/*
@@ -22152,6 +22166,14 @@ recordWholeRowDependencyOnOrError(Relation rel, const ObjectAddress *object, boo
 				systable_endscan(conscan);
 				table_close(conDesc, AccessShareLock);
 			}
+			if (find_wholerow && error_out)
+				ereport(ERROR,
+						errcode(ERRCODE_DATATYPE_MISMATCH),
+						errmsg("cannot alter table \"%s\" because constraint \"%s\" uses its row type",
+								RelationGetRelationName(rel),
+								constr_name),
+						errhint("You might need to drop constraint \"%s\" first",
+								constr_name));
 		}
 	}
 
@@ -22195,6 +22217,15 @@ recordWholeRowDependencyOnOrError(Relation rel, const ObjectAddress *object, boo
 				ReleaseSysCache(indexTuple);
 				continue;
 			}
+
+			if (find_wholerow && error_out)
+				ereport(ERROR,
+						errcode(ERRCODE_DATATYPE_MISMATCH),
+						errmsg("cannot alter table \"%s\" because index \"%s\" uses its row type",
+								RelationGetRelationName(rel),
+								get_rel_name(indexStruct->indexrelid)),
+						errhint("You might need to drop index \"%s\" first",
+								 get_rel_name(indexStruct->indexrelid)));
 		}
 
 		if (!heap_attisnull(indexTuple, Anum_pg_index_indexprs, NULL))
@@ -22222,6 +22253,15 @@ recordWholeRowDependencyOnOrError(Relation rel, const ObjectAddress *object, boo
 				/* record dependency for indexes that references whole-row */
 				recordDependencyOn(&idx_obj, object, DEPENDENCY_AUTO);
 			}
+
+			if (find_wholerow && error_out)
+				ereport(ERROR,
+						errcode(ERRCODE_DATATYPE_MISMATCH),
+						errmsg("cannot alter table \"%s\" because index \"%s\" uses its row type",
+								RelationGetRelationName(rel),
+								get_rel_name(indexStruct->indexrelid)),
+						errhint("You might need to drop index \"%s\" first",
+								 get_rel_name(indexStruct->indexrelid)));
 		}
 		ReleaseSysCache(indexTuple);
 	}
diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out
index 8930b4da7ce..4527016f319 100644
--- a/src/test/regress/expected/constraints.out
+++ b/src/test/regress/expected/constraints.out
@@ -271,6 +271,17 @@ ALTER TABLE DROP_COL_CHECK_TBL DROP COLUMN city;
 
 DROP TABLE DROP_COL_CHECK_TBL;
 --
+-- Change column data type should error out because the whole-row referenced
+-- check constraint is still need it.
+--
+CREATE TABLE ALTER_COL_CHECK_TBL (
+    city int, state text, is_capital bool, altitude int,
+    CONSTRAINT cc1 CHECK (ALTER_COL_CHECK_TBL is not null));
+ALTER TABLE ALTER_COL_CHECK_TBL ALTER COLUMN city SET DATA TYPE int8;
+ERROR:  cannot alter table "alter_col_check_tbl" because constraint "cc1" uses its row type
+HINT:  You might need to drop constraint "cc1" first
+DROP TABLE ALTER_COL_CHECK_TBL;
+--
 -- Check inheritance of defaults and constraints
 --
 CREATE TABLE INSERT_CHILD (cx INT default 42,
diff --git a/src/test/regress/expected/indexing.out b/src/test/regress/expected/indexing.out
index cac1cca3a1f..e544141ad9c 100644
--- a/src/test/regress/expected/indexing.out
+++ b/src/test/regress/expected/indexing.out
@@ -657,7 +657,13 @@ drop table idxpart, idxpart2;
 create table idxpart (a int, b int, c int);
 create index on idxpart(c);
 create index on idxpart((idxpart is not null));
+alter table idxpart alter column a set data type int8; --error
+ERROR:  cannot alter table "idxpart" because index "idxpart_expr_idx" uses its row type
+HINT:  You might need to drop index "idxpart_expr_idx" first
 create index on idxpart(a) where idxpart is not null;
+alter table idxpart alter column a set data type int8; --error
+ERROR:  cannot alter table "idxpart" because index "idxpart_expr_idx" uses its row type
+HINT:  You might need to drop index "idxpart_expr_idx" first
 alter table idxpart drop column c;
 \d idxpart
               Table "public.idxpart"
diff --git a/src/test/regress/sql/constraints.sql b/src/test/regress/sql/constraints.sql
index 676d8736101..bbe8328fbf9 100644
--- a/src/test/regress/sql/constraints.sql
+++ b/src/test/regress/sql/constraints.sql
@@ -176,6 +176,16 @@ ALTER TABLE DROP_COL_CHECK_TBL DROP COLUMN city;
 \d DROP_COL_CHECK_TBL
 DROP TABLE DROP_COL_CHECK_TBL;
 
+--
+-- Change column data type should error out because the whole-row referenced
+-- check constraint is still need it.
+--
+CREATE TABLE ALTER_COL_CHECK_TBL (
+    city int, state text, is_capital bool, altitude int,
+    CONSTRAINT cc1 CHECK (ALTER_COL_CHECK_TBL is not null));
+ALTER TABLE ALTER_COL_CHECK_TBL ALTER COLUMN city SET DATA TYPE int8;
+DROP TABLE ALTER_COL_CHECK_TBL;
+
 --
 -- Check inheritance of defaults and constraints
 --
diff --git a/src/test/regress/sql/indexing.sql b/src/test/regress/sql/indexing.sql
index 2bad9555112..0711860443e 100644
--- a/src/test/regress/sql/indexing.sql
+++ b/src/test/regress/sql/indexing.sql
@@ -298,7 +298,9 @@ drop table idxpart, idxpart2;
 create table idxpart (a int, b int, c int);
 create index on idxpart(c);
 create index on idxpart((idxpart is not null));
+alter table idxpart alter column a set data type int8; --error
 create index on idxpart(a) where idxpart is not null;
+alter table idxpart alter column a set data type int8; --error
 alter table idxpart drop column c;
 \d idxpart
 drop table idxpart;
-- 
2.34.1

---

From: jian he <[email-masked]>
Date: Tue, 23 Sep 2025 13:37:44 +0800

> to see if its vartype matches the whole-row type OID.

in v4, I use

+ TupleConstr *constr = RelationGetDescr(rel)->constr;
+
+ if (constr && constr->num_check > 0)
+{
+    systable_beginscan
+}
to check if a relation's check constraint expression contains a whole-row or
not.  however this will have multiple systable_beginscan if multiple check
constraints contain wholerow expr.

I changed it to systable_beginscan pg_constraint once and check if the scan
returned pg_constraint tuple meets our condition or not.

and some minor adjustments to regression tests.
From ea5f731dbd1c309a9a5a5d3119bd554e59b2a2ea Mon Sep 17 00:00:00 2001
From: jian he <[email-masked]>
Date: Tue, 23 Sep 2025 13:31:03 +0800
Subject: [PATCH v5 1/3] ALTER TABLE DROP COLUMN drop wholerow referenced
 object

now
\d ts
                 Table "public.ts"

discussion: https://postgr.es/m/[email-masked]
---
 src/backend/commands/tablecmds.c          | 176 +++++++++++++++++++++-
 src/test/regress/expected/constraints.out |  15 ++
 src/test/regress/expected/indexing.out    |  13 ++
 src/test/regress/sql/constraints.sql      |  11 ++
 src/test/regress/sql/indexing.sql         |   8 +
 5 files changed, 220 insertions(+), 3 deletions(-)

index 3be2e051d32..a3d1fe658c6 100644

 	ReleaseSysCache(tuple);

 
 	if (!recursing)
@@ -22062,3 +22080,155 @@ GetAttributeStorage(Oid atttypid, const char *storagemode)
 
 	return cstorage;
 }
+
+/*
+ * Record dependencies between whole-row objects (indexes, CHECK constraints)
+ * associated with relation and relation's ObjectAddress.
+ *
+ * error_out means can not install such dependency, we have to error out explicitly.
+ */
+static void
+recordWholeRowDependencyOnOrError(Relation rel, const ObjectAddress *object, bool error_out)
+{
+	Node	   *expr;
+	List	   *indexlist = NIL;
+	ObjectAddress idx_obj;
+	bool		find_wholerow = false;
+	ScanKeyData skey[1];
+	TupleConstr *constr = RelationGetDescr(rel)->constr;
+
+	/* check CHECK constraints contain whole-row references or not */
+	if (constr && constr->num_check > 0)
+	{
+		Relation	pg_constraint;
+		SysScanDesc conscan;
+		HeapTuple	htup;
+		ObjectAddress con_obj;
+
+		pg_constraint = table_open(ConstraintRelationId, AccessShareLock);
+
+		/* Search pg_constraint for relevant entries */
+		ScanKeyInit(&skey[0],
+					Anum_pg_constraint_conrelid,
+					BTEqualStrategyNumber, F_OIDEQ,
+					ObjectIdGetDatum(RelationGetRelid(rel)));
+
+		conscan = systable_beginscan(pg_constraint, ConstraintRelidTypidNameIndexId, true,
+									 NULL, 1, skey);
+		while (HeapTupleIsValid(htup = systable_getnext(conscan)))
+		{
+			Form_pg_constraint conform = (Form_pg_constraint) GETSTRUCT(htup);
+			Datum		val;
+			bool		isnull;
+			Bitmapset  *expr_attrs = NULL;
+
+			if (conform->contype != CONSTRAINT_CHECK)
+				continue;
+
+			/* Grab and test conbin is actually set */
+			val = fastgetattr(htup,
+							  Anum_pg_constraint_conbin,
+							  RelationGetDescr(pg_constraint), &isnull);
+			if (isnull)
+				elog(WARNING, "null conbin for relation \"%s\"",
+					 RelationGetRelationName(rel));
+			else
+			{
+				char	   *s = TextDatumGetCString(val);
+
+				expr = stringToNode(s);
+				pfree(s);
+
+				/* Find all attributes referenced */
+				pull_varattnos(expr, 1, &expr_attrs);
+
+				find_wholerow = bms_is_member(0 - FirstLowInvalidHeapAttributeNumber,
+											expr_attrs);
+				if (find_wholerow && !error_out)
+				{
+					con_obj.classId = ConstraintRelationId;
+					con_obj.objectId = conform->oid;
+					con_obj.objectSubId = 0;
+
+					/* record dependency for constraints that references whole-row */
+					recordDependencyOn(&con_obj, object, DEPENDENCY_AUTO);
+				}
+			}
+		}
+		systable_endscan(conscan);
+		table_close(pg_constraint, AccessShareLock);
+	}
+
+	/* check indexes contain whole-row references or not */
+	find_wholerow = false;
+	indexlist = RelationGetIndexList(rel);
+	foreach_oid(indexoid, indexlist)
+	{
+		HeapTuple	indexTuple;
+		Form_pg_index indexStruct;
+		Node	   *node;
+
+		indexTuple = SearchSysCache1(INDEXRELID, ObjectIdGetDatum(indexoid));
+		if (!HeapTupleIsValid(indexTuple))
+			elog(ERROR, "cache lookup failed for index %u", indexoid);
+		indexStruct = (Form_pg_index) GETSTRUCT(indexTuple);
+
+		if (!heap_attisnull(indexTuple, Anum_pg_index_indexprs, NULL))
+		{
+			Datum		exprDatum;
+			char	   *exprString;
+			Bitmapset  *expr_attrs = NULL;
+
+			/* Convert text string to node tree */
+			exprDatum = SysCacheGetAttrNotNull(INDEXRELID, indexTuple,
+											   Anum_pg_index_indexprs);
+			exprString = TextDatumGetCString(exprDatum);
+			node = (Node *) stringToNode(exprString);
+			pfree(exprString);
+
+			pull_varattnos(node, 1, &expr_attrs);
+
+			find_wholerow = bms_is_member(0 - FirstLowInvalidHeapAttributeNumber,
+										  expr_attrs);
+			if (find_wholerow && !error_out)
+			{
+				idx_obj.classId = RelationRelationId;
+				idx_obj.objectId = indexStruct->indexrelid;
+				idx_obj.objectSubId = 0;
+				/* record dependency for indexes that references whole-row */
+				recordDependencyOn(&idx_obj, object, DEPENDENCY_AUTO);
+
+				ReleaseSysCache(indexTuple);
+				continue;
+			}
+		}
+
+		if (!heap_attisnull(indexTuple, Anum_pg_index_indpred, NULL))
+		{
+			Datum		predDatum;
+			char	   *predString;
+			Bitmapset  *expr_attrs = NULL;
+
+			/* Convert text string to node tree */
+			predDatum = SysCacheGetAttrNotNull(INDEXRELID, indexTuple,
+											   Anum_pg_index_indpred);
+			predString = TextDatumGetCString(predDatum);
+			node = (Node *) stringToNode(predString);
+			pfree(predString);
+
+			pull_varattnos(node, 1, &expr_attrs);
+			find_wholerow = bms_is_member(0 - FirstLowInvalidHeapAttributeNumber,
+										  expr_attrs);
+			if (find_wholerow && !error_out)
+			{
+				idx_obj.classId = RelationRelationId;
+				idx_obj.objectId = indexStruct->indexrelid;
+				idx_obj.objectSubId = 0;
+
+				/* record dependency for indexes that references whole-row */
+				recordDependencyOn(&idx_obj, object, DEPENDENCY_AUTO);
+			}
+		}
+		ReleaseSysCache(indexTuple);
+	}
+}
diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out
index 3590d3274f0..d68f5a13e44 100644
--- a/src/test/regress/expected/constraints.out
+++ b/src/test/regress/expected/constraints.out
@@ -254,6 +254,21 @@ ERROR:  system column "ctid" reference in check constraint is invalid
 LINE 3:       CHECK (NOT (is_capital AND ctid::text = 'sys_col_check...
                                          ^
 --
+-- Drop column also drop check constraints that have whole-row reference
+--
+CREATE TABLE DROP_COL_CHECK_TBL (
+    city TEXT, state TEXT,
+    CONSTRAINT cc0 CHECK (DROP_COL_CHECK_TBL is null) NOT ENFORCED,
+    CONSTRAINT cc1 CHECK (DROP_COL_CHECK_TBL is not null) NOT ENFORCED);
+ALTER TABLE DROP_COL_CHECK_TBL DROP COLUMN city;
+\d DROP_COL_CHECK_TBL
+       Table "public.drop_col_check_tbl"
+ Column | Type | Collation | Nullable | Default 
+--------+------+-----------+----------+---------
+ state  | text |           |          | 
+
+DROP TABLE DROP_COL_CHECK_TBL;
+--
 -- Check inheritance of defaults and constraints
 --
 CREATE TABLE INSERT_CHILD (cx INT default 42,
diff --git a/src/test/regress/expected/indexing.out b/src/test/regress/expected/indexing.out
index 4d29fb85293..40512a8853a 100644
--- a/src/test/regress/expected/indexing.out
+++ b/src/test/regress/expected/indexing.out
@@ -654,6 +654,19 @@ alter table idxpart2 drop column c;
  b      | integer |           |          | 
 
+create index idxpart_idx1 on idxpart((idxpart is not null));
+create index idxpart_idx2 on idxpart(a) where idxpart is not null;
index 1f6dc8fd69f..34de5b3fd89 100644
 
+--
+-- Drop column also drop check constraints that have whole-row reference
+--
+CREATE TABLE DROP_COL_CHECK_TBL (
+    city TEXT, state TEXT,
+    CONSTRAINT cc0 CHECK (DROP_COL_CHECK_TBL is null) NOT ENFORCED,
+    CONSTRAINT cc1 CHECK (DROP_COL_CHECK_TBL is not null) NOT ENFORCED);
+ALTER TABLE DROP_COL_CHECK_TBL DROP COLUMN city;
+\d DROP_COL_CHECK_TBL
+DROP TABLE DROP_COL_CHECK_TBL;
+
 --
 -- Check inheritance of defaults and constraints
 --
diff --git a/src/test/regress/sql/indexing.sql b/src/test/regress/sql/indexing.sql
index b5cb01c2d70..9604495c0ec 100644
--- a/src/test/regress/sql/indexing.sql
+++ b/src/test/regress/sql/indexing.sql
@@ -295,6 +295,14 @@ alter table idxpart2 drop column c;
 \d idxpart2
 drop table idxpart, idxpart2;
 
+create table idxpart (a int, b int, c int);
+create index on idxpart(c);
+create index idxpart_idx1 on idxpart((idxpart is not null));
+create index idxpart_idx2 on idxpart(a) where idxpart is not null;
+alter table idxpart drop column c;
+\d idxpart
+drop table idxpart;
+
 -- Verify that expression indexes inherit correctly
 create table idxpart (a int, b int) partition by range (a);
 create table idxpart1 (like idxpart);
-- 
2.34.1

From 74ce2ccae810826bb080f014a546b0b011b86171 Mon Sep 17 00:00:00 2001
From: jian he <[email-masked]>
Date: Tue, 23 Sep 2025 13:33:30 +0800
Subject: [PATCH v5 2/3] disallow ALTER COLUMN SET DATA TYPE when wholerow
 referenced constraint exists

discussion: https://postgr.es/m/[email-masked]
---
 src/backend/commands/tablecmds.c          | 41 +++++++++++++++++++++++
 src/test/regress/expected/constraints.out | 11 ++++++
 src/test/regress/expected/indexing.out    |  9 +++++
 src/test/regress/sql/constraints.sql      | 10 ++++++
 src/test/regress/sql/indexing.sql         |  5 +++
 5 files changed, 76 insertions(+)

index a3d1fe658c6..9bf5d6bc2b6 100644
 
+	/*
+	 * If the table has a whole-row referenced CHECK constraint, indexes, then
+	 * changing column data type is not allowed.
+	*/
+	if (targettype != attTup->atttypid || targettypmod != attTup->atttypmod)
+	{
+		ObjectAddress object;
+
+		object.classId = RelationRelationId;
+		object.objectId = RelationGetRelid(rel);
+		object.objectSubId = attnum;
+		recordWholeRowDependencyOnOrError(rel, &object, true);
+	}
+
 	ReleaseSysCache(tuple);
 
 	/*
@@ -22153,6 +22167,15 @@ recordWholeRowDependencyOnOrError(Relation rel, const ObjectAddress *object, boo
 					/* record dependency for constraints that references whole-row */
 					recordDependencyOn(&con_obj, object, DEPENDENCY_AUTO);
 				}
+
+				if (find_wholerow && error_out)
+					ereport(ERROR,
+							errcode(ERRCODE_DATATYPE_MISMATCH),
+							errmsg("cannot alter table \"%s\" because constraint \"%s\" uses its row type",
+								   RelationGetRelationName(rel),
+								   NameStr(conform->conname)),
+							errhint("You might need to drop constraint \"%s\" first",
+									NameStr(conform->conname)));
 			}
 		}
 		systable_endscan(conscan);
@@ -22201,6 +22224,15 @@ recordWholeRowDependencyOnOrError(Relation rel, const ObjectAddress *object, boo
 				ReleaseSysCache(indexTuple);
 				continue;
 			}
+
+			if (find_wholerow && error_out)
+				ereport(ERROR,
+						errcode(ERRCODE_DATATYPE_MISMATCH),
+						errmsg("cannot alter table \"%s\" because index \"%s\" uses its row type",
+							   RelationGetRelationName(rel),
+							   get_rel_name(indexStruct->indexrelid)),
+						errhint("You might need to drop index \"%s\" first",
+								get_rel_name(indexStruct->indexrelid)));
 		}
 
 		if (!heap_attisnull(indexTuple, Anum_pg_index_indpred, NULL))
@@ -22228,6 +22260,15 @@ recordWholeRowDependencyOnOrError(Relation rel, const ObjectAddress *object, boo
 				/* record dependency for indexes that references whole-row */
 				recordDependencyOn(&idx_obj, object, DEPENDENCY_AUTO);
 			}
+
+			if (find_wholerow && error_out)
+				ereport(ERROR,
+						errcode(ERRCODE_DATATYPE_MISMATCH),
+						errmsg("cannot alter table \"%s\" because index \"%s\" uses its row type",
+							   RelationGetRelationName(rel),
+							   get_rel_name(indexStruct->indexrelid)),
+						errhint("You might need to drop index \"%s\" first",
+								get_rel_name(indexStruct->indexrelid)));
 		}
 		ReleaseSysCache(indexTuple);
 	}
diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out
index d68f5a13e44..c0f6593956f 100644
--- a/src/test/regress/expected/constraints.out
+++ b/src/test/regress/expected/constraints.out
@@ -269,6 +269,17 @@ ALTER TABLE DROP_COL_CHECK_TBL DROP COLUMN city;
 
+    city INT, state TEXT,
+    CONSTRAINT cc1 CHECK (ALTER_COL_CHECK_TBL is not null) NOT ENFORCED);
index 40512a8853a..4b683c679a8 100644
@@ -666,6 +666,15 @@ alter table idxpart drop column c;
  a      | integer |           |          | 
  b      | integer |           |          | 
 
+create index idxpart_idx1 on idxpart((idxpart is not null));
+create index idxpart_idx2 on idxpart(a) where idxpart is not null;
+alter table idxpart alter column a set data type int8; --error
+ERROR:  cannot alter table "idxpart" because index "idxpart_idx1" uses its row type
+HINT:  You might need to drop index "idxpart_idx1" first
+drop index idxpart_idx1;
+alter table idxpart alter column b set data type int8; --error
+ERROR:  cannot alter table "idxpart" because index "idxpart_idx2" uses its row type
+HINT:  You might need to drop index "idxpart_idx2" first
 drop table idxpart;
 -- Verify that expression indexes inherit correctly
 create table idxpart (a int, b int) partition by range (a);
diff --git a/src/test/regress/sql/constraints.sql b/src/test/regress/sql/constraints.sql
index 34de5b3fd89..18e120e7ce6 100644
--- a/src/test/regress/sql/constraints.sql
+++ b/src/test/regress/sql/constraints.sql
@@ -176,6 +176,16 @@ ALTER TABLE DROP_COL_CHECK_TBL DROP COLUMN city;
 \d DROP_COL_CHECK_TBL
 DROP TABLE DROP_COL_CHECK_TBL;
 
+--
+-- Change column data type should error out because the whole-row referenced
+-- check constraint is still need it.
+--
+CREATE TABLE ALTER_COL_CHECK_TBL (
+    city INT, state TEXT,
+    CONSTRAINT cc1 CHECK (ALTER_COL_CHECK_TBL is not null) NOT ENFORCED);
+ALTER TABLE ALTER_COL_CHECK_TBL ALTER COLUMN city SET DATA TYPE int8;
+DROP TABLE ALTER_COL_CHECK_TBL;
+
 --
 -- Check inheritance of defaults and constraints
 --
diff --git a/src/test/regress/sql/indexing.sql b/src/test/regress/sql/indexing.sql
index 9604495c0ec..b7c08c9ff5a 100644
--- a/src/test/regress/sql/indexing.sql
+++ b/src/test/regress/sql/indexing.sql
@@ -301,6 +301,11 @@ create index idxpart_idx1 on idxpart((idxpart is not null));
 create index idxpart_idx2 on idxpart(a) where idxpart is not null;
 alter table idxpart drop column c;
 \d idxpart
+create index idxpart_idx1 on idxpart((idxpart is not null));
+create index idxpart_idx2 on idxpart(a) where idxpart is not null;
+alter table idxpart alter column a set data type int8; --error
+drop index idxpart_idx1;
+alter table idxpart alter column b set data type int8; --error
 drop table idxpart;

From 0390378c6d90c7e77cdce81e9af5b86f360354fb Mon Sep 17 00:00:00 2001
From: jian he <[email-masked]>
Date: Tue, 23 Sep 2025 13:35:35 +0800
Subject: [PATCH v5 3/3] disallow change or drop column when wholerow
 referenced policy exists

demo:

discussion: https://postgr.es/m/[email-masked]
---
 src/backend/commands/tablecmds.c          | 94 ++++++++++++++++++++++-
 src/backend/optimizer/util/var.c          | 60 +++++++++++++++
 src/include/optimizer/optimizer.h         |  1 +
 src/test/regress/expected/rowsecurity.out | 27 +++++++
 src/test/regress/sql/rowsecurity.sql      | 17 ++++
 src/tools/pgindent/typedefs.list          |  1 +
 6 files changed, 198 insertions(+), 2 deletions(-)

diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 9bf5d6bc2b6..797cba12ab9 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -22096,8 +22096,8 @@ GetAttributeStorage(Oid atttypid, const char *storagemode)
 }
 
 /*
- * Record dependencies between whole-row objects (indexes, CHECK constraints)
- * associated with relation and relation's ObjectAddress.
+ * Record dependencies between whole-row objects (indexes, CHECK constraints or
+ * policies) associated with relation and relation's ObjectAddress.
  *
  * error_out means can not install such dependency, we have to error out explicitly.
  */
@@ -22107,8 +22107,13 @@ recordWholeRowDependencyOnOrError(Relation rel, const ObjectAddress *object, boo
 	Node	   *expr;
 	List	   *indexlist = NIL;
 	ObjectAddress idx_obj;
+	ObjectAddress pol_obj;
 	bool		find_wholerow = false;
 	ScanKeyData skey[1];
+	Relation	pg_policy;
+	SysScanDesc sscan;
+	HeapTuple	policy_tuple;
+	Oid			reltypid;
 	TupleConstr *constr = RelationGetDescr(rel)->constr;
 
 	/* check CHECK constraints contain whole-row references or not */
@@ -22272,4 +22277,89 @@ recordWholeRowDependencyOnOrError(Relation rel, const ObjectAddress *object, boo
 		}
+	/* Search pg_policy for whole-row references entries */
+						errhint("You might need to drop security policy \"%s\" first",
+						errhint("You might need to drop security policy \"%s\" first",
index 8065237a189..e5e0c4edde5 100644
@@ -49,6 +49,11 @@ typedef struct
 	int			sublevels_up;
 } pull_vars_context;
 
+typedef struct
+{
+	Oid			reltypid;	/* the whole-row typeid */
+} contain_wholerow_context;
+
 typedef struct
 {
 	int			var_location;
@@ -73,6 +78,7 @@ typedef struct
 static bool pull_varnos_walker(Node *node,
 							   pull_varnos_context *context);
 static bool pull_varattnos_walker(Node *node, pull_varattnos_context *context);
+static bool ExprContainWholeRow_walker(Node *node, contain_wholerow_context *context);
 static bool pull_vars_walker(Node *node, pull_vars_context *context);
 static bool contain_var_clause_walker(Node *node, void *context);
 static bool contain_vars_of_level_walker(Node *node, int *sublevels_up);
@@ -327,6 +333,60 @@ pull_varattnos_walker(Node *node, pull_varattnos_context *context)
 	return expression_tree_walker(node, pull_varattnos_walker, context);
 }
 
+static bool
+ * to detect a whole-row Var. But if the node contains sublinks (unplanned
+ * subqueries), the check must instead rely on the whole-row type OID.
+ * Use ExprContainWholeRow to check whole-row var existsence when in doubt.
 
 /*
  * pull_vars_of_level
diff --git a/src/include/optimizer/optimizer.h b/src/include/optimizer/optimizer.h
index 04878f1f1c2..69d5e6905da 100644
--- a/src/include/optimizer/optimizer.h
+++ b/src/include/optimizer/optimizer.h
@@ -194,6 +194,7 @@ extern SortGroupClause *get_sortgroupref_clause_noerr(Index sortref,
 extern Bitmapset *pull_varnos(PlannerInfo *root, Node *node);
 extern Bitmapset *pull_varnos_of_level(PlannerInfo *root, Node *node, int levelsup);
 extern void pull_varattnos(Node *node, Index varno, Bitmapset **varattnos);
+extern bool ExprContainWholeRow(Node *node, Oid reltypid);
 extern List *pull_vars_of_level(Node *node, int levelsup);
 extern bool contain_var_clause(Node *node);
 extern bool contain_vars_of_level(Node *node, int levelsup);
diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out
index 8c879509313..f3be08e9743 100644
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -2357,6 +2357,33 @@ SELECT * FROM document;
   14 |  11 |      1 | regress_rls_bob   | new novel                        | 
 (16 rows)
 
+--check drop column (no CASCADE) or alter column data type will fail because of
+--whole-row referenced security policy exists.
+ALTER TABLE document ADD COLUMN dummy INT4, ADD COLUMN dummy1 INT4;
+CREATE POLICY p7 ON document AS PERMISSIVE
+    USING (cid IS NOT NULL AND
+    (WITH cte AS (SELECT TRUE FROM uaccount
+        WHERE EXISTS (SELECT document FROM uaccount WHERE uaccount IS NULL))
+    SELECT * FROM cte));
+ALTER TABLE document ALTER COLUMN dummy SET DATA TYPE BIGINT; --error
+ERROR:  cannot alter table "document" because security policy "p7" uses its row type
+HINT:  You might need to drop security policy "p7" first
+ALTER TABLE document DROP COLUMN dummy; --error
+ERROR:  cannot drop column dummy of table document because other objects depend on it
+DETAIL:  policy p7 on table document depends on column dummy of table document
+HINT:  Use DROP ... CASCADE to drop the dependent objects too.
+ALTER TABLE document DROP COLUMN dummy CASCADE; --ok
+NOTICE:  drop cascades to policy p7 on table document
+CREATE POLICY p8 ON document FOR INSERT WITH CHECK (document IS NOT NULL);
+ALTER TABLE document ALTER COLUMN dummy1 SET DATA TYPE BIGINT; --error
+ERROR:  cannot alter table "document" because security policy "p8" uses its row type
+HINT:  You might need to drop security policy "p8" first
+ALTER TABLE document DROP COLUMN dummy1; --error
+ERROR:  cannot drop column dummy1 of table document because other objects depend on it
+DETAIL:  policy p8 on table document depends on column dummy1 of table document
+HINT:  Use DROP ... CASCADE to drop the dependent objects too.
+ALTER TABLE document DROP COLUMN dummy1 CASCADE; --ok
+NOTICE:  drop cascades to policy p8 on table document
 --
 -- ROLE/GROUP
 --
diff --git a/src/test/regress/sql/rowsecurity.sql b/src/test/regress/sql/rowsecurity.sql
index 21ac0ca51ee..ead0a5e62be 100644
--- a/src/test/regress/sql/rowsecurity.sql
+++ b/src/test/regress/sql/rowsecurity.sql
@@ -1021,6 +1021,23 @@ DROP POLICY p1 ON document;
 -- Just check everything went per plan
 SELECT * FROM document;
 
+--check drop column (no CASCADE) or alter column data type will fail because of
+--whole-row referenced security policy exists.
+ALTER TABLE document ADD COLUMN dummy INT4, ADD COLUMN dummy1 INT4;
+CREATE POLICY p7 ON document AS PERMISSIVE
+    USING (cid IS NOT NULL AND
+    (WITH cte AS (SELECT TRUE FROM uaccount
+        WHERE EXISTS (SELECT document FROM uaccount WHERE uaccount IS NULL))
+    SELECT * FROM cte));
+ALTER TABLE document ALTER COLUMN dummy SET DATA TYPE BIGINT; --error
+ALTER TABLE document DROP COLUMN dummy; --error
+ALTER TABLE document DROP COLUMN dummy CASCADE; --ok
+
+CREATE POLICY p8 ON document FOR INSERT WITH CHECK (document IS NOT NULL);
+ALTER TABLE document ALTER COLUMN dummy1 SET DATA TYPE BIGINT; --error
+ALTER TABLE document DROP COLUMN dummy1; --error
+ALTER TABLE document DROP COLUMN dummy1 CASCADE; --ok
+
 --
 -- ROLE/GROUP
 --
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index e90af5b2ad3..3db0a0beed1 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -3521,6 +3521,7 @@ conn_oauth_scope_func
 conn_sasl_state_func
 contain_aggs_of_level_context
 contain_placeholder_references_context
+contain_wholerow_context
 convert_testexpr_context
 copy_data_dest_cb
 copy_data_source_cb
-- 
2.34.1

---

From: jian he <[email-masked]>
Date: Tue, 23 Dec 2025 09:49:26 +0800

Hi,

I have consolidated the work into two patches.
0001 handles indexes and CHECK constraints that contain whole-row references.
0002 handles policies that contain whole-row references.

The difference is that, for policy objects, we cannot use pull_varattnos to find
whole-row references, since we need recurse to Sublink node, Also, a policy’s
whole-row reference may point to an arbitrary relation, while index, check
constraint can only reference the relation it is associated with.

so the previous v5-0003 scans pg_policy.polrelid to find out whether it's safe
to drop one relation is wrong, we should use pg_depend.

summary:
For objects (indexes, constraints, policies) that contain whole-row references:
ALTER TABLE DROP COLUMN will drop these objects too.

ALTER COLUMN SET DATA TYPE will error out, saying that the data type cannot be
changed because whole-row–dependent objects exist.

--
jian
https://www.enterprisedb.com/
From 63bcfe1baad72a166f957c1b6ed70d88f6b469cd Mon Sep 17 00:00:00 2001
From: jian he <[email-masked]>
Date: Mon, 22 Dec 2025 23:28:00 +0800
Subject: [PATCH v6 1/2] fix DDL wholerow referenced constraints and indexes
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

1. ALTER TABLE DROP COLUMN.
ALTER TABLE DROP COLUMN should remove indexes or constraints contain
whole-row expression.
We enumerate all constraints and indexes and check one by one to determine
whether it contains a whole-row Var reference. If such a reference is found, we
record the dependency and later performMultipleDeletions will do the job.

for example:
CREATE TABLE ts (a int, constraint cc check((ts = ROW(1))));
CREATE INDEX tsi3 on ts ((ts is null));
ALTER TABLE DROP COLUMN should drop above all indexes, constraints on table ts.

2. ALTER COLUMN SET DATA TYPE
ALTER COLUMN SET DATA TYPE should error out when whole-row referenced object
(index, constraints, etc) exists.
ALTER COLUMN SET DATA TYPE fundamentally changes the table’s record type; At
present, we cannot compare records that contain columns of dissimilar types, see
function record_eq.  As a result, ALTER COLUMN SET DATA TYPE does not work for
whole-row reference objects (such as constraints and indexes), and must
therefore raise an error.

For example, below ALTER COLUMN SET DATA TYPE should fail.
CREATE TABLE ts (a int, CONSTRAINT cc CHECK ((ts = ROW(1))));
CREATE INDEX ON ts ((ts IS NOT NULL));
ALTER TABLE ts ALTER COLUMN a SET DATA TYPE int8;

discussion: https://postgr.es/m/[email-masked]
---
 src/backend/commands/tablecmds.c          | 248 +++++++++++++++++++++-
 src/test/regress/expected/constraints.out |  26 +++
 src/test/regress/expected/indexing.out    |  22 ++
 src/test/regress/sql/constraints.sql      |  21 ++
 src/test/regress/sql/indexing.sql         |  13 ++
 5 files changed, 327 insertions(+), 3 deletions(-)

diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 6b1a00ed477..a541b795d48 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -745,6 +745,9 @@ static void ATExecMergePartitions(List **wqueue, AlteredTableInfo *tab, Relation
 static void ATExecSplitPartition(List **wqueue, AlteredTableInfo *tab,
 								 Relation rel, PartitionCmd *cmd,
 								 AlterTableUtilityContext *context);
+static void recordWholeRowDependencyOnOrError(Relation rel,
+											  const ObjectAddress *object,
+											  bool error_out);
 
 /* ----------------------------------------------------------------
  *		DefineRelation
@@ -9393,6 +9396,21 @@ ATExecDropColumn(List **wqueue, Relation rel, const char *colName,
 
 	ReleaseSysCache(tuple);
 
+	object.classId = RelationRelationId;
+	object.objectId = RelationGetRelid(rel);
+	object.objectSubId = attnum;
+
+	/*
+	 * We should also remove indexes or constraints that contain whole-row
+	 * expression. Using recordWholeRowDependencyOnOrError to establish a
+	 * dependency between the column and any constraint or index involving
+	 * whole-row Vars.  performMultipleDeletions will then take care of
+	 * removing them later.
+	 */
+	recordWholeRowDependencyOnOrError(rel, &object, false);
+
+	CommandCounterIncrement();
+
 	/*
 	 * Propagate to children as appropriate.  Unlike most other ALTER
 	 * routines, we have to do this one level of recursion at a time; we can't
@@ -9486,9 +9504,6 @@ ATExecDropColumn(List **wqueue, Relation rel, const char *colName,
 	}

 	if (!recursing)
@@ -14773,6 +14788,7 @@ ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel,
 	SysScanDesc scan;
 	HeapTuple	depTup;
 	ObjectAddress address;
+	ObjectAddress object;
 
 	/*
 	 * Clear all the missing values if we're rewriting the table, since this
@@ -14868,6 +14884,16 @@ ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel,
 	 */
 	RememberAllDependentForRebuilding(tab, AT_AlterColumnType, rel, attnum, colName);
 
+	object.classId = RelationRelationId;
+	object.objectId = RelationGetRelid(rel);
+	object.objectSubId = attnum;
+
+	/*
+	 * Check for whole-row referenced objects (constraints, indexes etc) --
+	 * can't cope
+	 */
+	recordWholeRowDependencyOnOrError(rel, &object, true);
+
 	/*
 	 * Now scan for dependencies of this column on other things.  The only
 	 * things we should find are the dependency on the column datatype and
@@ -23337,3 +23363,219 @@ ATExecSplitPartition(List **wqueue, AlteredTableInfo *tab, Relation rel,
 	/* Restore the userid and security context. */
 	SetUserIdAndSecContext(save_userid, save_sec_context);
 }
+
+/*
+ * Record dependencies between whole-row objects (indexes, CHECK constraints)
+ * and the relation's ObjectAddress.
+ *
+ * error_out means can not install such dependency, error out explicitly.
+ */
+static void
+recordWholeRowDependencyOnOrError(Relation rel, const ObjectAddress *object, bool error_out)
+{
+	Node	   *expr = NULL;
+	ScanKeyData skey;
+	Relation	pg_index;
+	SysScanDesc indscan;
+	HeapTuple	htup;
+	ObjectAddress idx_obj;
+	HeapTuple	indexTuple;
+	Form_pg_index indexStruct;
+	List	   *indexlist = NIL;
+	Bitmapset  *expr_attrs = NULL;
+	Datum		exprDatum;
+	char	   *exprString;
+	bool		isnull;
+	bool		find_wholerow = false;
+	TupleConstr *constr = RelationGetDescr(rel)->constr;
+
+	/*
+	 * Loop through each CHECK constraint, see if it contain whole-row
+	 * references or not
+	 */
+	if (constr && constr->num_check > 0)
+	{
+		Relation	pg_constraint;
+		SysScanDesc conscan;
+		ObjectAddress con_obj;
+
+		pg_constraint = table_open(ConstraintRelationId, AccessShareLock);
+
+		/* Search pg_constraint for relevant entries */
+		ScanKeyInit(&skey,
+					Anum_pg_constraint_conrelid,
+					BTEqualStrategyNumber, F_OIDEQ,
+					ObjectIdGetDatum(RelationGetRelid(rel)));
+
+		conscan = systable_beginscan(pg_constraint, ConstraintRelidTypidNameIndexId, true,
+									 NULL, 1, &skey);
+		while (HeapTupleIsValid(htup = systable_getnext(conscan)))
+		{
+			Form_pg_constraint conform = (Form_pg_constraint) GETSTRUCT(htup);
+
+			if (conform->contype != CONSTRAINT_CHECK)
+				continue;
+
+			/* Grab and test conbin is actually set */
+			exprDatum = fastgetattr(htup,
+									Anum_pg_constraint_conbin,
+									RelationGetDescr(pg_constraint), &isnull);
+			if (isnull)
+				elog(WARNING, "null conbin for relation \"%s\"",
+					 RelationGetRelationName(rel));
+			else
+			{
+				char	   *s = TextDatumGetCString(exprDatum);
+
+				expr = stringToNode(s);
+				pfree(s);
+
+				/* Find all attributes referenced */
+				pull_varattnos(expr, 1, &expr_attrs);
+
+				find_wholerow = bms_is_member(0 - FirstLowInvalidHeapAttributeNumber,
+											  expr_attrs);
+
+				if (find_wholerow)
+				{
+					if (error_out)
+						ereport(ERROR,
+								errcode(ERRCODE_DATATYPE_MISMATCH),
+								errmsg("cannot alter table \"%s\" because constraint \"%s\" uses its row type",
+									   RelationGetRelationName(rel),
+									   NameStr(conform->conname)),
+								errhint("You might need to drop constraint \"%s\" first",
+										NameStr(conform->conname)));
+					else
+					{
+						con_obj.classId = ConstraintRelationId;
+						con_obj.objectId = conform->oid;
+						con_obj.objectSubId = 0;
+
+						/*
+						 * record dependency for constraints that references
+						 * whole-row
+						 */
+						recordDependencyOn(&con_obj, object, DEPENDENCY_AUTO);
+					}
+				}
+			}
+		}
+		systable_endscan(conscan);
+		table_close(pg_constraint, AccessShareLock);
+	}
+
+	/* now checking indexes contain whole-row references or not */
+	/* Prepare to scan pg_index for entries having indrelid = this rel */
+	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(htup = systable_getnext(indscan)))
+	{
+		Form_pg_index index = (Form_pg_index) GETSTRUCT(htup);
+
+		/* add index's OID to result list */
+		indexlist = lappend_oid(indexlist, index->indexrelid);
+	}
+	systable_endscan(indscan);
+
+	table_close(pg_index, AccessShareLock);
+
+	foreach_oid(indexoid, indexlist)
+	{
+		indexTuple = SearchSysCache1(INDEXRELID, ObjectIdGetDatum(indexoid));
+		if (!HeapTupleIsValid(indexTuple))
+			elog(ERROR, "cache lookup failed for index %u", indexoid);
+		indexStruct = (Form_pg_index) GETSTRUCT(indexTuple);
+
+		if (!heap_attisnull(indexTuple, Anum_pg_index_indexprs, NULL))
+		{
+			expr_attrs = NULL;
+
+			/* Convert text string to node tree */
+			exprDatum = SysCacheGetAttrNotNull(INDEXRELID, indexTuple,
+											   Anum_pg_index_indexprs);
+			exprString = TextDatumGetCString(exprDatum);
+			expr = (Node *) stringToNode(exprString);
+			pfree(exprString);
+
+			pull_varattnos(expr, 1, &expr_attrs);
+
+			find_wholerow = bms_is_member(0 - FirstLowInvalidHeapAttributeNumber,
+										  expr_attrs);
+
+			if (find_wholerow)
+			{
+				if (error_out)
+					ereport(ERROR,
+							errcode(ERRCODE_DATATYPE_MISMATCH),
+							errmsg("cannot alter table \"%s\" because index \"%s\" uses its row type",
+								   RelationGetRelationName(rel),
+								   get_rel_name(indexStruct->indexrelid)),
+							errhint("You might need to drop index \"%s\" first",
+									get_rel_name(indexStruct->indexrelid)));
+				else
+				{
+					idx_obj.classId = RelationRelationId;
+					idx_obj.objectId = indexStruct->indexrelid;
+					idx_obj.objectSubId = 0;
+
+					/* record dependency for indexes that references whole-row */
+					recordDependencyOn(&idx_obj, object, DEPENDENCY_AUTO);
+
+					ReleaseSysCache(indexTuple);
+
+					continue;
+				}
+			}
+		}
+
+		if (!heap_attisnull(indexTuple, Anum_pg_index_indpred, NULL))
+		{
+			expr_attrs = NULL;
+
+			/* Convert text string to node tree */
+			exprDatum = SysCacheGetAttrNotNull(INDEXRELID, indexTuple,
+											   Anum_pg_index_indpred);
+			exprString = TextDatumGetCString(exprDatum);
+			expr = (Node *) stringToNode(exprString);
+			pfree(exprString);
+
+			pull_varattnos(expr, 1, &expr_attrs);
+
+			find_wholerow = bms_is_member(0 - FirstLowInvalidHeapAttributeNumber,
+										  expr_attrs);
+			if (find_wholerow)
+			{
+				if (error_out)
+					ereport(ERROR,
+							errcode(ERRCODE_DATATYPE_MISMATCH),
+							errmsg("cannot alter table \"%s\" because index \"%s\" uses its row type",
+								   RelationGetRelationName(rel),
+								   get_rel_name(indexStruct->indexrelid)),
+							errhint("You might need to drop index \"%s\" first",
+									get_rel_name(indexStruct->indexrelid)));
+				else
+				{
+					idx_obj.classId = RelationRelationId;
+					idx_obj.objectId = indexStruct->indexrelid;
+					idx_obj.objectSubId = 0;
+
+					/* record dependency for indexes that references whole-row */
+					recordDependencyOn(&idx_obj, object, DEPENDENCY_AUTO);
+
+					ReleaseSysCache(indexTuple);
+
+					continue;
+				}
+			}
+		}
+		ReleaseSysCache(indexTuple);
+	}
+}
diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out
index 1bbf59cca02..6e26e9fbcfc 100644
--- a/src/test/regress/expected/constraints.out
+++ b/src/test/regress/expected/constraints.out
@@ -254,6 +254,32 @@ ERROR:  system column "ctid" reference in check constraint is invalid
 LINE 3:       CHECK (NOT (is_capital AND ctid::text = 'sys_col_check...
                                          ^
 --
+-- Drop column also drop check constraints that have whole-row reference
+--
+CREATE TABLE DROP_COL_CHECK_TBL (
+    city TEXT, state TEXT,
+    CONSTRAINT cc0 CHECK (DROP_COL_CHECK_TBL is null) NOT ENFORCED,
+    CONSTRAINT cc1 CHECK (DROP_COL_CHECK_TBL is not null) NOT ENFORCED);
+ALTER TABLE DROP_COL_CHECK_TBL DROP COLUMN city;
+\d DROP_COL_CHECK_TBL
+       Table "public.drop_col_check_tbl"
+ Column | Type | Collation | Nullable | Default 
+--------+------+-----------+----------+---------
+ state  | text |           |          | 
+
+DROP TABLE DROP_COL_CHECK_TBL;
+--
+-- Change column data type should error out because the whole-row referenced
+-- check constraint is still need it.
+--
+CREATE TABLE ALTER_COL_CHECK_TBL (
+    city INT, state TEXT,
+    CONSTRAINT cc1 CHECK (ALTER_COL_CHECK_TBL is not null) NOT ENFORCED);
+ALTER TABLE ALTER_COL_CHECK_TBL ALTER COLUMN city SET DATA TYPE int8;
+ERROR:  cannot alter table "alter_col_check_tbl" because constraint "cc1" uses its row type
+HINT:  You might need to drop constraint "cc1" first
+DROP TABLE ALTER_COL_CHECK_TBL;
+--
 -- Check inheritance of defaults and constraints
 --
 CREATE TABLE INSERT_CHILD (cx INT default 42,
diff --git a/src/test/regress/expected/indexing.out b/src/test/regress/expected/indexing.out
index 4d29fb85293..4b683c679a8 100644
--- a/src/test/regress/expected/indexing.out
+++ b/src/test/regress/expected/indexing.out
@@ -654,6 +654,28 @@ alter table idxpart2 drop column c;
  b      | integer |           |          | 
 
index 733a1dbccfe..c7ae3fb6641 100644
@@ -165,6 +165,27 @@ CREATE TABLE SYS_COL_CHECK_TBL (city text, state text, is_capital bool,
 
+--
index b5cb01c2d70..b7c08c9ff5a 100644
@@ -295,6 +295,19 @@ alter table idxpart2 drop column c;

From 96c1fb6ca674368d4d9aea0980a6af1658abd5cd Mon Sep 17 00:00:00 2001
From: jian he <[email-masked]>
Date: Mon, 22 Dec 2025 23:25:07 +0800
Subject: [PATCH v6 2/2] disallow ALTER TABLE ALTER COLUMN when wholerow
 referenced policy exists

Policy have a DEPENDENCY_NORMAL type with their source table.  Policy's qual and
with check qual are quite unconstrained (allowing subqueries), we can't reliably
use pull_varattnos to detect if they contain subqueries.  A further complication
is that the qual and with check qual whole-row Var may not only references their
own table but also for other unrelated tables.

Therefore We should check pg_depend, not pg_policy, to see if dropping this
table affects any policy objects. After collecting the policies impacted by the
ALTER TABLE command, check each policy qual and with check qual, see if
whole-row references or not.

demo:
CREATE TABLE rls_tbl (a int, b int, c int);
CREATE TABLE t (a int);
CREATE POLICY p1 ON rls_tbl USING (rls_tbl >= ROW(1,1,1) and (select t is null from t));

ALTER TABLE t DROP COLUMN a; --error
ERROR:  cannot drop column a of table t because other objects depend on it
DETAIL:  policy p1 on table rls_tbl depends on column a of table t
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

ALTER TABLE t ALTER COLUMN a SET DATA TYPE BIGINT; --error
ERROR:  cannot alter table "t" because security policy "p1" uses its row type
HINT:  You might need to drop security policy "p1" first

discussion: https://postgr.es/m/[email-masked]
---
 src/backend/commands/tablecmds.c          | 147 +++++++++++++++++++++-
 src/backend/optimizer/util/var.c          |  59 +++++++++
 src/include/optimizer/optimizer.h         |   1 +
 src/test/regress/expected/rowsecurity.out |  29 ++++-
 src/test/regress/sql/rowsecurity.sql      |  17 +++
 src/tools/pgindent/typedefs.list          |   1 +
 6 files changed, 251 insertions(+), 3 deletions(-)

diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index a541b795d48..6fdacab7c66 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -749,6 +749,8 @@ static void recordWholeRowDependencyOnOrError(Relation rel,
 											  const ObjectAddress *object,
 											  bool error_out);
 
+static List *GetAllPoliciesRelations(Relation rel);
+
 /* ----------------------------------------------------------------
  *		DefineRelation
  *				Creates a new relation.
@@ -23365,7 +23367,7 @@ ATExecSplitPartition(List **wqueue, AlteredTableInfo *tab, Relation rel,
 }
 
 /*
- * Record dependencies between whole-row objects (indexes, CHECK constraints)
+ * Record dependencies between whole-row objects (indexes, CHECK constraints, policies)
  * and the relation's ObjectAddress.
  *
  * error_out means can not install such dependency, error out explicitly.
@@ -23386,6 +23388,9 @@ recordWholeRowDependencyOnOrError(Relation rel, const ObjectAddress *object, boo
 	Datum		exprDatum;
 	char	   *exprString;
 	bool		isnull;
+	List	   *pols = NIL;
+	Relation	pg_policy;
+	Oid			reltypid;
 	bool		find_wholerow = false;
 	TupleConstr *constr = RelationGetDescr(rel)->constr;
 
@@ -23578,4 +23583,144 @@ recordWholeRowDependencyOnOrError(Relation rel, const ObjectAddress *object, boo
 		}
 		ReleaseSysCache(indexTuple);
 	}
+
+	reltypid = get_rel_type_id(RelationGetRelid(rel));
+
+	pg_policy = table_open(PolicyRelationId, AccessShareLock);
+
+	pols = GetAllPoliciesRelations(rel);
+
+	foreach_oid(policyoid, pols)
+	{
+		ObjectAddress pol_obj;
+		SysScanDesc sscan;
+		HeapTuple	policy_tuple;
+		ScanKeyData polskey[1];
+
+		ScanKeyInit(&polskey[0],
+					Anum_pg_policy_oid,
+					BTEqualStrategyNumber, F_OIDEQ,
+					ObjectIdGetDatum(policyoid));
+		sscan = systable_beginscan(pg_policy,
+								   PolicyOidIndexId, true, NULL, 1,
+								   polskey);
+		while (HeapTupleIsValid(policy_tuple = systable_getnext(sscan)))
+		{
+			Form_pg_policy policy = (Form_pg_policy) GETSTRUCT(policy_tuple);
+
+			/* Get policy qual */
+			exprDatum = heap_getattr(policy_tuple, Anum_pg_policy_polqual,
+									 RelationGetDescr(pg_policy), &isnull);
+			if (!isnull)
+			{
+				exprString = TextDatumGetCString(exprDatum);
+				expr = (Node *) stringToNode(exprString);
+				pfree(exprString);
+
+				find_wholerow = ExprContainWholeRow(expr, reltypid);
+
+				if (find_wholerow && !error_out)
+				{
+					pol_obj.classId = PolicyRelationId;
+					pol_obj.objectId = policy->oid;
+					pol_obj.objectSubId = 0;
+
+					/*
+					 * record dependency for policies that references
+					 * whole-row Var
+					 */
+					recordDependencyOn(&pol_obj, object, DEPENDENCY_NORMAL);
+
+					continue;
+				}
+
+				if (find_wholerow && error_out)
+					ereport(ERROR,
+							errcode(ERRCODE_DATATYPE_MISMATCH),
+							errmsg("cannot alter table \"%s\" because security policy \"%s\" uses its row type",
+								   RelationGetRelationName(rel),
+								   NameStr(policy->polname)),
+							errhint("You might need to drop security policy \"%s\" first",
+									NameStr(policy->polname)));
+			}
+
+			exprDatum = heap_getattr(policy_tuple, Anum_pg_policy_polwithcheck,
+									 RelationGetDescr(pg_policy), &isnull);
+			if (!isnull)
+			{
+				exprString = TextDatumGetCString(exprDatum);
+				expr = (Node *) stringToNode(exprString);
+				pfree(exprString);
+
+				find_wholerow = ExprContainWholeRow(expr, reltypid);
+
+				if (find_wholerow && !error_out)
+				{
+					pol_obj.classId = PolicyRelationId;
+					pol_obj.objectId = policy->oid;
+					pol_obj.objectSubId = 0;
+
+					/*
+					 * record dependency for policies that references
+					 * whole-row Var
+					 */
+					recordDependencyOn(&pol_obj, object, DEPENDENCY_NORMAL);
+				}
+
+				if (find_wholerow && error_out)
+					ereport(ERROR,
+							errcode(ERRCODE_DATATYPE_MISMATCH),
+							errmsg("cannot alter table \"%s\" because security policy \"%s\" uses its row type",
+								   RelationGetRelationName(rel),
+								   NameStr(policy->polname)),
+							errhint("You might need to drop security policy \"%s\" first",
+									NameStr(policy->polname)));
+			}
+		}
+		systable_endscan(sscan);
+	}
+	table_close(pg_policy, AccessShareLock);
+}
+
+static List *
+GetAllPoliciesRelations(Relation rel)
+{
+	Relation	depRel;
+	ScanKeyData key[3];
+	SysScanDesc scan;
+	HeapTuple	depTup;
+	List	   *result = NIL;
+
+	depRel = table_open(DependRelationId, RowExclusiveLock);
+	ScanKeyInit(&key[0],
+				Anum_pg_depend_refclassid,
+				BTEqualStrategyNumber, F_OIDEQ,
+				ObjectIdGetDatum(RelationRelationId));
+	ScanKeyInit(&key[1],
+				Anum_pg_depend_refobjid,
+				BTEqualStrategyNumber, F_OIDEQ,
+				ObjectIdGetDatum(RelationGetRelid(rel)));
+	ScanKeyInit(&key[2],
+				Anum_pg_depend_refobjsubid,
+				BTEqualStrategyNumber, F_INT4EQ,
+				Int32GetDatum((int32) 0));
+
+	scan = systable_beginscan(depRel, DependReferenceIndexId, true,
+							  NULL, 3, key);
+	while (HeapTupleIsValid(depTup = systable_getnext(scan)))
+	{
+		Form_pg_depend foundDep = (Form_pg_depend) GETSTRUCT(depTup);
+		ObjectAddress foundObject;
+
+		foundObject.classId = foundDep->classid;
+		foundObject.objectId = foundDep->objid;
+		foundObject.objectSubId = foundDep->objsubid;
+
+		if (foundObject.classId == PolicyRelationId)
+			result = list_append_unique_oid(result, foundObject.objectId);
+	}
+	systable_endscan(scan);
+	table_close(depRel, NoLock);
+
+	return result;
 }
diff --git a/src/backend/optimizer/util/var.c b/src/backend/optimizer/util/var.c
index 8065237a189..244c866c170 100644
--- a/src/backend/optimizer/util/var.c
+++ b/src/backend/optimizer/util/var.c
@@ -49,6 +49,11 @@ typedef struct
 	int			sublevels_up;
 } pull_vars_context;
 
+typedef struct
+{
@@ -327,6 +333,59 @@ pull_varattnos_walker(Node *node, pull_varattnos_context *context)
 
+static bool
 
 /*
  * pull_vars_of_level
diff --git a/src/include/optimizer/optimizer.h b/src/include/optimizer/optimizer.h
index 44ec5296a18..34b8e7facb7 100644
--- a/src/include/optimizer/optimizer.h
+++ b/src/include/optimizer/optimizer.h
@@ -199,6 +199,7 @@ extern SortGroupClause *get_sortgroupref_clause_noerr(Index sortref,
 extern Bitmapset *pull_varnos(PlannerInfo *root, Node *node);
 extern Bitmapset *pull_varnos_of_level(PlannerInfo *root, Node *node, int levelsup);
 extern void pull_varattnos(Node *node, Index varno, Bitmapset **varattnos);
+extern bool ExprContainWholeRow(Node *node, Oid reltypid);
 extern List *pull_vars_of_level(Node *node, int levelsup);
 extern bool contain_var_clause(Node *node);
 extern bool contain_vars_of_level(Node *node, int levelsup);
diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out
index c958ef4d70a..1eff6e55ef3 100644
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -2637,6 +2637,32 @@ SELECT * FROM document;
   14 |  11 |      1 | regress_rls_bob   | new novel                        | 
 (16 rows)
 
+--check drop column (no CASCADE) or alter column data type will fail because of
+--whole-row referenced security policy exists.
+DROP TABLE  part_document;
+ALTER TABLE document ADD COLUMN dummy INT4, ADD COLUMN dummy1 INT4;
+CREATE POLICY p7 ON document AS PERMISSIVE
+    USING (cid IS NOT NULL AND
+    (WITH cte AS (SELECT TRUE FROM uaccount
+        WHERE EXISTS (SELECT document FROM uaccount WHERE uaccount IS NULL))
+    SELECT * FROM cte));
+ALTER TABLE uaccount ALTER COLUMN seclv SET DATA TYPE BIGINT; --errror
+ERROR:  cannot alter table "uaccount" because security policy "p7" uses its row type
+HINT:  You might need to drop security policy "p7" first
+ALTER TABLE document ALTER COLUMN dummy SET DATA TYPE BIGINT; --error
+ERROR:  cannot alter table "document" because security policy "p7" uses its row type
+HINT:  You might need to drop security policy "p7" first
+ALTER TABLE document DROP COLUMN dummy; --error
+ERROR:  cannot drop column dummy of table document because other objects depend on it
+DETAIL:  policy p7 on table document depends on column dummy of table document
+HINT:  Use DROP ... CASCADE to drop the dependent objects too.
+ALTER TABLE uaccount DROP COLUMN seclv; --error
+ERROR:  cannot drop column seclv of table uaccount because other objects depend on it
+DETAIL:  policy p7 on table document depends on column seclv of table uaccount
+HINT:  Use DROP ... CASCADE to drop the dependent objects too.
+ALTER TABLE document DROP COLUMN dummy CASCADE; --ok
+NOTICE:  drop cascades to policy p7 on table document
+ALTER TABLE uaccount DROP COLUMN seclv CASCADE; --ok
 --
 -- ROLE/GROUP
 --
@@ -5105,12 +5131,11 @@ drop table rls_t, test_t;
 --
 RESET SESSION AUTHORIZATION;
 DROP SCHEMA regress_rls_schema CASCADE;
-NOTICE:  drop cascades to 30 other objects
+NOTICE:  drop cascades to 29 other objects
 DETAIL:  drop cascades to function f_leak(text)
 drop cascades to table uaccount
 drop cascades to table category
 drop cascades to table document
-drop cascades to table part_document
 drop cascades to table dependent
 drop cascades to table rec1
 drop cascades to table rec2
diff --git a/src/test/regress/sql/rowsecurity.sql b/src/test/regress/sql/rowsecurity.sql
index 5d923c5ca3b..76487b5e4ba 100644
--- a/src/test/regress/sql/rowsecurity.sql
+++ b/src/test/regress/sql/rowsecurity.sql
@@ -1163,6 +1163,23 @@ DROP POLICY p1 ON document;
 -- Just check everything went per plan
 SELECT * FROM document;
 
+--check drop column (no CASCADE) or alter column data type will fail because of
+--whole-row referenced security policy exists.
+DROP TABLE  part_document;
+ALTER TABLE document ADD COLUMN dummy INT4, ADD COLUMN dummy1 INT4;
+CREATE POLICY p7 ON document AS PERMISSIVE
+    USING (cid IS NOT NULL AND
+    (WITH cte AS (SELECT TRUE FROM uaccount
+        WHERE EXISTS (SELECT document FROM uaccount WHERE uaccount IS NULL))
+    SELECT * FROM cte));
+ALTER TABLE uaccount ALTER COLUMN seclv SET DATA TYPE BIGINT; --errror
+ALTER TABLE document ALTER COLUMN dummy SET DATA TYPE BIGINT; --error
+
+ALTER TABLE document DROP COLUMN dummy; --error
+ALTER TABLE uaccount DROP COLUMN seclv; --error
+ALTER TABLE document DROP COLUMN dummy CASCADE; --ok
+ALTER TABLE uaccount DROP COLUMN seclv CASCADE; --ok
+
 --
 -- ROLE/GROUP
 --
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 04845d5e680..1fc231b0c60 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -3574,6 +3574,7 @@ conn_oauth_scope_func
 conn_sasl_state_func
 contain_aggs_of_level_context
 contain_placeholder_references_context
+contain_wholerow_context
 convert_testexpr_context
 copy_data_dest_cb
 copy_data_source_cb
-- 
2.34.1

---

From: jian he <[email-masked]>
Date: Sat, 27 Dec 2025 11:00:36 +0800

hi.

CREATE FUNCTION dummy_trigger() RETURNS TRIGGER AS $$
  BEGIN
    RETURN NULL;
  END
$$ language plpgsql;

create table main_table(a int);
CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON main_table
FOR EACH ROW
WHEN (new.a > 0)
EXECUTE PROCEDURE dummy_trigger();

ALTER TABLE main_table ALTER COLUMN a SET DATA TYPE INT8; --error
ALTER TABLE main_table DROP COLUMN a; --error

Dropping a column or changing its data type will fail if the column is
referenced in a trigger’s WHEN clause, that's the current behavior.
I think we should expand that to a whole-row reference WHEN clause in trigger.

DROP TRIGGER before_ins_stmt_trig ON main_table;
CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON main_table
FOR EACH ROW
WHEN (new is null)
EXECUTE PROCEDURE dummy_trigger();
ALTER TABLE main_table ALTER COLUMN a SET DATA TYPE INT8; --expect to error
ALTER TABLE main_table DROP COLUMN a;  --expect to error

new summary:
For (constraints, indexes, policies, triggers) that contain whole-row
references:
ALTER TABLE DROP COLUMN [CASCADE] will drop these objects too.

ALTER COLUMN SET DATA TYPE will error out because whole-row–dependent objects
exist.

---

From: lakshmi <[email-masked]>
Date: Thu, 30 Apr 2026 10:27:00 +0530

Hi,

I tested the latest v8-0001 patch on current master and wanted to share my
observations.

Before applying the patch, when dropping a column, constraints and indexes
that referenced the whole row (for example, CHECK (ts = ROW(...)) or
expressions using ts) were not removed. This left the table in an
inconsistent state and resulted in errors during inserts.

After applying the patch, these objects are correctly detected and removed
when the column is dropped. The table remains clean, and inserts work as
expected.

I also tried a few additional scenarios:

   -

   DROP COLUMN with CASCADE—behaved as expected, no leftover objects
   -

   normal column-level constraints — still handled correctly (no regression)
   -

   multiple whole-row constraints — all removed properly
   -

   ALTER COLUMN TYPE — correctly throws an error when a whole-row
   constraint exists

Overall, the behavior looks correct and consistent based on these tests.

Thanks for working on this!
lakshmi

---

From: jian he <[email-masked]>
Date: Mon, 25 May 2026 14:43:56 +0800

Hi.

[1] rebuilds whole-row dependencies for ALTER COLUMN SET EXPRESSION,
this thread addresses whole-row dependencies for ALTER TABLE DROP
COLUMN and ALTER COLUMN SET DATA TYPE.

Overall, the attached v9 doesn't include any major changes.
It just contains some refactoring to make the coding style consistent with [1].

[1] https://commitfest.postgresql.org/patch/6755

--
jian
https://www.enterprisedb.com/
From 10760e35806f8ac5608e246eb8d0b4baa5779f4f Mon Sep 17 00:00:00 2001
From: jian he <[email-masked]>
Date: Mon, 25 May 2026 14:19:45 +0800
Subject: [PATCH v9 2/3] fix DDL wholerow referenced triggers

ALTER TABLE DROP COLUMN should fail if any trigger's WHEN clause contains a
whole-row reference. To do this, we record a dependency between the
relation and the dependent trigger in RememberWholeRowDependentForRebuilding.
Later, performMultipleDeletions handles the deletion logic.

ALTER COLUMN SET DATA TYPE fundamentally changes the table's row type.  Rows
with different column data types cannot be compared (see record_eq). Therefore,
we must error out otherwise, any trigger WHEN clause contains whole-row
references may constantly error.

discussion: https://postgr.es/m/[email-masked]
commitfest: https://commitfest.postgresql.org/patch/6055
---
 src/backend/commands/tablecmds.c           | 38 ++++++++++++++++++++++
 src/test/regress/expected/foreign_data.out | 14 ++++++++
 src/test/regress/expected/triggers.out     | 28 ++++++++++++++++
 src/test/regress/sql/foreign_data.sql      | 10 ++++++
 src/test/regress/sql/triggers.sql          | 17 ++++++++++
 5 files changed, 107 insertions(+)

diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 4aa582511bc..bd8e29de1b1 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -24156,4 +24156,42 @@ RememberWholeRowDependentForRebuilding(AlteredTableInfo *tab, AlterTableType sub
 	}
 	systable_endscan(indscan);
 	table_close(pg_index, AccessShareLock);
+
+	/* Now checking trigger whole-row references */
+	if (rel->trigdesc != NULL)
+	{
+		for (int i = 0; i < rel->trigdesc->numtriggers; i++)
+		{
+			Bitmapset  *expr_attrs = NULL;
+			Trigger    *trig = &rel->trigdesc->triggers[i];
+
+			if (trig->tgqual == NULL)
+				continue;
+
+			expr = stringToNode(trig->tgqual);
+
+			pull_varattnos(expr, PRS2_OLD_VARNO, &expr_attrs);
+
+			pull_varattnos(expr, PRS2_NEW_VARNO, &expr_attrs);
+
+			/*
+			 * If the triger WHEN qual contains whole-row reference then
+			 * remember it
+			 */
+			if (bms_is_member(InvalidAttrNumber - FirstLowInvalidHeapAttributeNumber,
+							  expr_attrs))
+			{
+				ObjectAddress trig_obj;
+
+				ObjectAddressSet(trig_obj, TriggerRelationId, trig->tgoid);
+
+				/*
+				 * The dependency between the trigger and its relation is
+				 * DEPENDENCY_NORMAL
+				 */
+				RememberWholeRowDependent(tab, subtype, rel, attnum,
+										  &trig_obj, DEPENDENCY_NORMAL);
+			}
+		}
+	}
 }
diff --git a/src/test/regress/expected/foreign_data.out b/src/test/regress/expected/foreign_data.out
index d8e4cb12c3d..4e2acad9dd9 100644
--- a/src/test/regress/expected/foreign_data.out
+++ b/src/test/regress/expected/foreign_data.out
@@ -1427,6 +1427,20 @@ DROP TRIGGER trigtest_before_stmt ON foreign_schema.foreign_table_1;
 DROP TRIGGER trigtest_before_row ON foreign_schema.foreign_table_1;
 DROP TRIGGER trigtest_after_stmt ON foreign_schema.foreign_table_1;
 DROP TRIGGER trigtest_after_row ON foreign_schema.foreign_table_1;
+CREATE TRIGGER trigtest_before_stmt BEFORE INSERT OR UPDATE
+ON foreign_schema.foreign_table_1
+FOR EACH ROW
+WHEN (new IS NOT NULL)
+EXECUTE PROCEDURE dummy_trigger();
+ALTER FOREIGN TABLE foreign_schema.foreign_table_1 ALTER COLUMN c7 SET DATA TYPE bigint; -- error
+ERROR:  cannot alter table "foreign_table_1" because trigger trigtest_before_stmt on foreign table foreign_schema.foreign_table_1 uses its row type
+HINT:  You might need to drop trigger trigtest_before_stmt on foreign table foreign_schema.foreign_table_1 first
+ALTER FOREIGN TABLE foreign_schema.foreign_table_1 DROP COLUMN c7; -- error
+ERROR:  cannot drop column c7 of foreign table foreign_schema.foreign_table_1 because other objects depend on it
+DETAIL:  trigger trigtest_before_stmt on foreign table foreign_schema.foreign_table_1 depends on column c7 of foreign table foreign_schema.foreign_table_1
+HINT:  Use DROP ... CASCADE to drop the dependent objects too.
+ALTER FOREIGN TABLE foreign_schema.foreign_table_1 DROP COLUMN c7 CASCADE; -- ok
+NOTICE:  drop cascades to trigger trigtest_before_stmt on foreign table foreign_schema.foreign_table_1
 DROP FUNCTION dummy_trigger();
 -- Table inheritance
 CREATE TABLE fd_pt1 (
diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out
index 8fcb33ac81a..e10a43e5f3f 100644
--- a/src/test/regress/expected/triggers.out
+++ b/src/test/regress/expected/triggers.out
@@ -227,6 +227,34 @@ ERROR:  trigger "no_such_trigger" for table "main_table" does not exist
 COMMENT ON TRIGGER before_ins_stmt_trig ON main_table IS 'right';
 COMMENT ON TRIGGER before_ins_stmt_trig ON main_table IS NULL;
 --
+-- test triggers with WHEN clause contain wholerow reference
+--
+CREATE TABLE test_tbl1 (a int, b int) PARTITION BY RANGE (a);
+CREATE TABLE test_tbl1p1 PARTITION OF test_tbl1 FOR VALUES FROM (0) TO (1000);
+CREATE TRIGGER test_tbl1p1_trig
+    BEFORE INSERT OR UPDATE ON test_tbl1p1 FOR EACH ROW
+    WHEN (new = ROW (1, 1))
+    EXECUTE PROCEDURE trigger_func ('test_tbl1p1');
+ALTER TABLE test_tbl1 ALTER COLUMN b SET DATA TYPE bigint; -- error
+ERROR:  cannot alter table "test_tbl1p1" because trigger test_tbl1p1_trig on table test_tbl1p1 uses its row type
+HINT:  You might need to drop trigger test_tbl1p1_trig on table test_tbl1p1 first
+ALTER TABLE test_tbl1 DROP COLUMN b; -- error
+ERROR:  cannot drop desired object(s) because other objects depend on them
+DETAIL:  trigger test_tbl1p1_trig on table test_tbl1p1 depends on column b of table test_tbl1p1
+HINT:  Use DROP ... CASCADE to drop the dependent objects too.
+ALTER TABLE test_tbl1 DROP COLUMN b CASCADE; -- ok
+NOTICE:  drop cascades to trigger test_tbl1p1_trig on table test_tbl1p1
+\d+ test_tbl1
+                           Partitioned table "public.test_tbl1"
+ Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ a      | integer |           |          |         | plain   |              | 
+Partition key: RANGE (a)
+Partitions:
+    test_tbl1p1 FOR VALUES FROM (0) TO (1000)
+
+DROP TABLE test_tbl1;
+--
 -- test triggers with WHEN clause
 --
 CREATE TRIGGER modified_a BEFORE UPDATE OF a ON main_table
diff --git a/src/test/regress/sql/foreign_data.sql b/src/test/regress/sql/foreign_data.sql
index ed78052b1e2..5f2eb0e3279 100644
--- a/src/test/regress/sql/foreign_data.sql
+++ b/src/test/regress/sql/foreign_data.sql
@@ -649,6 +649,16 @@ DROP TRIGGER trigtest_before_row ON foreign_schema.foreign_table_1;
 DROP TRIGGER trigtest_after_stmt ON foreign_schema.foreign_table_1;
 DROP TRIGGER trigtest_after_row ON foreign_schema.foreign_table_1;
 
+CREATE TRIGGER trigtest_before_stmt BEFORE INSERT OR UPDATE
+ON foreign_schema.foreign_table_1
+FOR EACH ROW
+WHEN (new IS NOT NULL)
+EXECUTE PROCEDURE dummy_trigger();
+
+ALTER FOREIGN TABLE foreign_schema.foreign_table_1 ALTER COLUMN c7 SET DATA TYPE bigint; -- error
+ALTER FOREIGN TABLE foreign_schema.foreign_table_1 DROP COLUMN c7; -- error
+ALTER FOREIGN TABLE foreign_schema.foreign_table_1 DROP COLUMN c7 CASCADE; -- ok
+
 DROP FUNCTION dummy_trigger();
 
 -- Table inheritance
diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sql
index 2285e90110e..1823c2cfb8d 100644
--- a/src/test/regress/sql/triggers.sql
+++ b/src/test/regress/sql/triggers.sql
@@ -158,6 +158,23 @@ COMMENT ON TRIGGER no_such_trigger ON main_table IS 'wrong';
 COMMENT ON TRIGGER before_ins_stmt_trig ON main_table IS 'right';
 COMMENT ON TRIGGER before_ins_stmt_trig ON main_table IS NULL;
 
+--
+-- test triggers with WHEN clause contain wholerow reference
+--
+CREATE TABLE test_tbl1 (a int, b int) PARTITION BY RANGE (a);
+CREATE TABLE test_tbl1p1 PARTITION OF test_tbl1 FOR VALUES FROM (0) TO (1000);
+
+CREATE TRIGGER test_tbl1p1_trig
+    BEFORE INSERT OR UPDATE ON test_tbl1p1 FOR EACH ROW
+    WHEN (new = ROW (1, 1))
+    EXECUTE PROCEDURE trigger_func ('test_tbl1p1');
+
+ALTER TABLE test_tbl1 ALTER COLUMN b SET DATA TYPE bigint; -- error
+ALTER TABLE test_tbl1 DROP COLUMN b; -- error
+ALTER TABLE test_tbl1 DROP COLUMN b CASCADE; -- ok
+\d+ test_tbl1
+DROP TABLE test_tbl1;
+
 --
 -- test triggers with WHEN clause
 --
-- 
2.34.1

From 575265b67813e860a382971d5e62fae9c745ba72 Mon Sep 17 00:00:00 2001
From: jian he <[email-masked]>
Date: Sat, 23 May 2026 21:40:23 +0800
Subject: [PATCH v9 3/3] fix DDL wholerow referenced policies

ALTER TABLE DROP COLUMN must fail if any policy's WHEN clause contains a
whole-row reference. To achieve this, we record a dependency between the
relation and the dependent policy in RememberWholeRowDependentForRebuilding.
Later, performMultipleDeletions handles the deletion logic.

ALTER COLUMN SET DATA TYPE fundamentally changes the table's row type.  Rows
with different column data types cannot be compared (see record_eq). Therefore,
we must error out otherwise, any policy WHEN clause contains whole-row
references may constantly error.

discussion: https://postgr.es/m/[email-masked]
commitfest: https://commitfest.postgresql.org/patch/6055
---
 src/backend/commands/tablecmds.c          | 130 ++++++++++++++++++++++
 src/backend/optimizer/util/var.c          |  56 ++++++++++
 src/include/optimizer/optimizer.h         |   1 +
 src/test/regress/expected/rowsecurity.out |  29 ++++-
 src/test/regress/sql/rowsecurity.sql      |  17 +++
 src/tools/pgindent/typedefs.list          |   1 +
 6 files changed, 232 insertions(+), 2 deletions(-)

diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index bd8e29de1b1..ed30c372239 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -790,6 +790,8 @@ static void RememberWholeRowDependentForRebuilding(AlteredTableInfo *tab, AlterT
 												   Relation rel, AttrNumber attnum,
 												   const char *colName);
 
+static List *GetAllRelAssociatedPolicies(Relation rel);
+
 /* ----------------------------------------------------------------
  *		DefineRelation
  *				Creates a new relation.
@@ -23976,6 +23978,7 @@ RememberWholeRowDependentForRebuilding(AlteredTableInfo *tab, AlterTableType sub
 	ScanKeyData skey;
 	Relation	pg_index;
 	Relation	pg_constraint;
+	Relation	pg_policy;
 	SysScanDesc indscan;
 	SysScanDesc conscan;
 	HeapTuple	constrTuple;
@@ -23984,6 +23987,8 @@ RememberWholeRowDependentForRebuilding(AlteredTableInfo *tab, AlterTableType sub
 	char	   *exprString = NULL;
 	bool		isnull;
 	List	   *wholerow_idxoids = NIL;
+	List	   *pols = NIL;
+	Oid			reltypid;
 
 	Assert(subtype == AT_AlterColumnType || subtype == AT_DropColumn);
 
@@ -24194,4 +24199,129 @@ RememberWholeRowDependentForRebuilding(AlteredTableInfo *tab, AlterTableType sub
 			}
 		}
 	}
+
+	/* Now checking policy whole-row references */
+	reltypid = get_rel_type_id(RelationGetRelid(rel));
+
+	pg_policy = table_open(PolicyRelationId, AccessShareLock);
+
+	pols = GetAllRelAssociatedPolicies(rel);
+
+	foreach_oid(policyoid, pols)
+	{
+		SysScanDesc sscan;
+		HeapTuple	policy_tuple;
+		ScanKeyData polskey[1];
+
+		ScanKeyInit(&polskey[0],
+					Anum_pg_policy_oid,
+					BTEqualStrategyNumber, F_OIDEQ,
+					ObjectIdGetDatum(policyoid));
+		sscan = systable_beginscan(pg_policy,
+								   PolicyOidIndexId,
+								   true,
+								   NULL,
+								   1,
+								   polskey);
+		while (HeapTupleIsValid(policy_tuple = systable_getnext(sscan)))
+		{
+			Form_pg_policy policy = (Form_pg_policy) GETSTRUCT(policy_tuple);
+
+			exprDatum = heap_getattr(policy_tuple, Anum_pg_policy_polqual,
+									 RelationGetDescr(pg_policy),
+									 &isnull);
+			if (!isnull)
+			{
+				exprString = TextDatumGetCString(exprDatum);
+				expr = (Node *) stringToNode(exprString);
+				pfree(exprString);
+
+				if (expr_contain_wholerow(expr, reltypid))
+				{
+					ObjectAddress pol_obj;
+
+					ObjectAddressSet(pol_obj, PolicyRelationId, policy->oid);
+
+					/*
+					 * The dependency between the policy and it's relation is
+					 * DEPENDENCY_NORMAL
+					 */
+					RememberWholeRowDependent(tab, subtype, rel, attnum,
+											  &pol_obj, DEPENDENCY_NORMAL);
+
+					continue;
+				}
+			}
+
+			exprDatum = heap_getattr(policy_tuple, Anum_pg_policy_polwithcheck,
+									 RelationGetDescr(pg_policy),
+									 &isnull);
+			if (!isnull)
+			{
+				exprString = TextDatumGetCString(exprDatum);
+				expr = (Node *) stringToNode(exprString);
+				pfree(exprString);
+
+				if (expr_contain_wholerow(expr, reltypid))
+				{
+					ObjectAddress pol_obj;
+
+					ObjectAddressSet(pol_obj, PolicyRelationId, policy->oid);
+
+					/*
+					 * The dependency between the policy and it's relation is
+					 * DEPENDENCY_NORMAL
+					 */
+					RememberWholeRowDependent(tab, subtype, rel, attnum,
+											  &pol_obj, DEPENDENCY_NORMAL);
+				}
+			}
+		}
+		systable_endscan(sscan);
+	}
+	table_close(pg_policy, AccessShareLock);
+}
+
+/*
+ * GetAllRelAssociatedPolicies
+ *
+ * Returns a list of OIDs of all row-level security policies associated with the
+ * given relation.
+ */
+static List *
+GetAllRelAssociatedPolicies(Relation rel)
+{
+	Relation	depRel;
+	ScanKeyData key[3];
+	SysScanDesc scan;
+	HeapTuple	depTup;
+	List	   *result = NIL;
+
+	depRel = table_open(DependRelationId, AccessShareLock);
+	ScanKeyInit(&key[0],
+				Anum_pg_depend_refclassid,
+				BTEqualStrategyNumber, F_OIDEQ,
+				ObjectIdGetDatum(RelationRelationId));
+	ScanKeyInit(&key[1],
+				Anum_pg_depend_refobjid,
+				BTEqualStrategyNumber, F_OIDEQ,
+				ObjectIdGetDatum(RelationGetRelid(rel)));
+	ScanKeyInit(&key[2],
+				Anum_pg_depend_refobjsubid,
+				BTEqualStrategyNumber, F_INT4EQ,
+				Int32GetDatum((int32) 0));
+
+	scan = systable_beginscan(depRel, DependReferenceIndexId, true,
+							  NULL, 3, key);
+	while (HeapTupleIsValid(depTup = systable_getnext(scan)))
+	{
+		Form_pg_depend foundDep = (Form_pg_depend) GETSTRUCT(depTup);
+
+		if (foundDep->classid == PolicyRelationId)
+			result = list_append_unique_oid(result, foundDep->objid);
+	}
+	systable_endscan(scan);
+	table_close(depRel, AccessShareLock);
+
+	return result;
 }
diff --git a/src/backend/optimizer/util/var.c b/src/backend/optimizer/util/var.c
index 907a255c36f..75f19271974 100644
--- a/src/backend/optimizer/util/var.c
+++ b/src/backend/optimizer/util/var.c
@@ -49,6 +49,11 @@ typedef struct
 	int			sublevels_up;
 } pull_vars_context;
 
+typedef struct
+{
+static bool expr_contain_wholerow_walker(Node *node, contain_wholerow_context *context);
@@ -327,6 +333,56 @@ pull_varattnos_walker(Node *node, pull_varattnos_context *context)
 
+static bool
+expr_contain_wholerow_walker(Node *node, contain_wholerow_context *context)
+{
+	if (node == NULL)
+		return false;
+
+	if (IsA(node, Var))
+	{
+		Var		   *var = (Var *) node;
+
+		if (var->varattno == InvalidAttrNumber &&
+			var->vartype == context->reltypid)
+			return true;
+
+		return false;
+	}
+
+	if (IsA(node, Query))
+		return query_tree_walker((Query *) node, expr_contain_wholerow_walker,
+								 context, 0);
+
+	return expression_tree_walker(node, expr_contain_wholerow_walker, context);
+}
+
+/*
+ * expr_contain_wholerow -
+ *
+ * Determine whether an expression contains whole-row Var reference, recursing as needed.
+ * For simple expressions without sublinks, pull_varattnos is usually sufficient
+ * to detect a whole-row Var. But if the node contains sublinks (unplanned
+ * subqueries), the check must instead rely on the whole-row type OID.
+ *
+ * Use expr_contain_wholerow to check whole-row var existsence when in doubt.
+ */
+bool
+expr_contain_wholerow(Node *node, Oid reltypid)
+{
+	contain_wholerow_context context;
+
+	context.reltypid = reltypid;
+
+	Assert(OidIsValid(reltypid));
+
+	return query_or_expression_tree_walker(node,
+										   expr_contain_wholerow_walker,
+										   &context,
+										   0);
+}
+
+
 
 /*
  * pull_vars_of_level
diff --git a/src/include/optimizer/optimizer.h b/src/include/optimizer/optimizer.h
index cb6241e2bdd..0491059c7cc 100644
--- a/src/include/optimizer/optimizer.h
+++ b/src/include/optimizer/optimizer.h
@@ -206,6 +206,7 @@ extern SortGroupClause *get_sortgroupref_clause_noerr(Index sortref,
 extern Bitmapset *pull_varnos(PlannerInfo *root, Node *node);
 extern Bitmapset *pull_varnos_of_level(PlannerInfo *root, Node *node, int levelsup);
 extern void pull_varattnos(Node *node, Index varno, Bitmapset **varattnos);
+extern bool expr_contain_wholerow(Node *node, Oid reltypid);
 extern List *pull_vars_of_level(Node *node, int levelsup);
 extern bool contain_var_clause(Node *node);
 extern bool contain_vars_of_level(Node *node, int levelsup);
diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out
index 3a5e82c35bd..6b6fe994033 100644
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -2731,6 +2731,32 @@ SELECT * FROM document;
   14 |  11 |      1 | regress_rls_bob   | new novel                        | 
 (16 rows)
 
+-- check drop column (no CASCADE) or alter column data type will fail because of
+-- whole-row referenced security policy exists.
+DROP TABLE  part_document;
+ALTER TABLE document ADD COLUMN dummy INT4, ADD COLUMN dummy1 INT4;
+CREATE POLICY p7 ON document AS PERMISSIVE
+    USING (cid IS NOT NULL AND
+    (WITH cte AS (SELECT TRUE FROM uaccount
+        WHERE EXISTS (SELECT document FROM uaccount WHERE uaccount IS NULL))
+    SELECT * FROM cte));
+ALTER TABLE uaccount ALTER COLUMN seclv SET DATA TYPE BIGINT; -- error
+ERROR:  cannot alter table "uaccount" because policy p7 on table document uses its row type
+HINT:  You might need to drop policy p7 on table document first
+ALTER TABLE document ALTER COLUMN dummy SET DATA TYPE BIGINT; -- error
+ERROR:  cannot alter table "document" because policy p7 on table document uses its row type
+HINT:  You might need to drop policy p7 on table document first
+ALTER TABLE document DROP COLUMN dummy; -- error
+ERROR:  cannot drop column dummy of table document because other objects depend on it
+DETAIL:  policy p7 on table document depends on column dummy of table document
+HINT:  Use DROP ... CASCADE to drop the dependent objects too.
+ALTER TABLE uaccount DROP COLUMN seclv; -- error
+ERROR:  cannot drop column seclv of table uaccount because other objects depend on it
+DETAIL:  policy p7 on table document depends on column seclv of table uaccount
+HINT:  Use DROP ... CASCADE to drop the dependent objects too.
+ALTER TABLE document DROP COLUMN dummy CASCADE; -- ok
+NOTICE:  drop cascades to policy p7 on table document
+ALTER TABLE uaccount DROP COLUMN seclv CASCADE; -- ok
 --
 -- ROLE/GROUP
 --
@@ -5199,12 +5225,11 @@ drop table rls_t, test_t;
 --
 RESET SESSION AUTHORIZATION;
 DROP SCHEMA regress_rls_schema CASCADE;
-NOTICE:  drop cascades to 30 other objects
+NOTICE:  drop cascades to 29 other objects
 DETAIL:  drop cascades to function f_leak(text)
 drop cascades to table uaccount
 drop cascades to table category
 drop cascades to table document
-drop cascades to table part_document
 drop cascades to table dependent
 drop cascades to table rec1
 drop cascades to table rec2
diff --git a/src/test/regress/sql/rowsecurity.sql b/src/test/regress/sql/rowsecurity.sql
index 6b3566271df..32f2dda73a4 100644
--- a/src/test/regress/sql/rowsecurity.sql
+++ b/src/test/regress/sql/rowsecurity.sql
@@ -1219,6 +1219,23 @@ DROP POLICY p1 ON document;
 -- Just check everything went per plan
 SELECT * FROM document;
 
+-- check drop column (no CASCADE) or alter column data type will fail because of
+-- whole-row referenced security policy exists.
+DROP TABLE  part_document;
+ALTER TABLE document ADD COLUMN dummy INT4, ADD COLUMN dummy1 INT4;
+CREATE POLICY p7 ON document AS PERMISSIVE
+    USING (cid IS NOT NULL AND
+    (WITH cte AS (SELECT TRUE FROM uaccount
+        WHERE EXISTS (SELECT document FROM uaccount WHERE uaccount IS NULL))
+    SELECT * FROM cte));
+ALTER TABLE uaccount ALTER COLUMN seclv SET DATA TYPE BIGINT; -- error
+ALTER TABLE document ALTER COLUMN dummy SET DATA TYPE BIGINT; -- error
+
+ALTER TABLE document DROP COLUMN dummy; -- error
+ALTER TABLE uaccount DROP COLUMN seclv; -- error
+ALTER TABLE document DROP COLUMN dummy CASCADE; -- ok
+ALTER TABLE uaccount DROP COLUMN seclv CASCADE; -- ok
+
 --
 -- ROLE/GROUP
 --
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 8cf40c87043..9236525b44d 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -3692,6 +3692,7 @@ config_handle
 config_var_value
 contain_aggs_of_level_context
 contain_placeholder_references_context
+contain_wholerow_context
 convert_testexpr_context
 copy_data_dest_cb
 copy_data_source_cb
-- 
2.34.1

From edd9bb976d37dbd00c66d8ffeb6b91da49cf83ec Mon Sep 17 00:00:00 2001
From: jian he <[email-masked]>
Date: Mon, 25 May 2026 14:22:45 +0800
Subject: [PATCH v9 1/3] fix DDL wholerow referenced constraints and indexes
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

ALTER TABLE DROP COLUMN should remove indexes or constraints contain whole-row
references.

discussion: https://postgr.es/m/[email-masked]
commitfest: https://commitfest.postgresql.org/patch/6055
---
 src/backend/commands/tablecmds.c          | 255 ++++++++++++++++++++++
 src/test/regress/expected/constraints.out |  23 ++
 src/test/regress/expected/indexing.out    |  32 +++
 src/test/regress/sql/constraints.sql      |  17 ++
 src/test/regress/sql/indexing.sql         |  17 ++
 5 files changed, 344 insertions(+)

diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 1e0bacf85fc..4aa582511bc 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
@@ -9395,6 +9398,10 @@ ATExecDropColumn(List **wqueue, Relation rel, const char *colName,
 	List	   *children;
 	ObjectAddress object;
 	bool		is_expr;
+	AlteredTableInfo *tab;
+
+	/* Find or create work queue entry for this table */
+	tab = ATGetQueueEntry(wqueue, rel);
 
 	/* At top level, permission check was done in ATPrepCmd, else do it */
 	if (recursing)
@@ -9467,6 +9474,15 @@ ATExecDropColumn(List **wqueue, Relation rel, const char *colName,
 
 	ReleaseSysCache(tuple);
 
+	/*
+	 * Record dependencies between this relation and any objects containing
+	 * whole-row Var references. performMultipleDeletions will take care of
+	 * removing them dependencies later.
+	 */
+	RememberWholeRowDependentForRebuilding(tab, AT_DropColumn, rel, attnum, colName);
+
+	CommandCounterIncrement();
+
 	/*
 	 * Propagate to children as appropriate.  Unlike most other ALTER
 	 * routines, we have to do this one level of recursion at a time; we can't
@@ -15118,6 +15134,15 @@ ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel,
 	 */
 	RememberAllDependentForRebuilding(tab, AT_AlterColumnType, rel, attnum, colName);
 
+	/*
+	 * Record dependencies between this relation and any objects containing
+	 * whole-row Var references. We either error out saying such a dependency
+	 * is not allowed, or we install the dependencies.
+	 */
+	RememberWholeRowDependentForRebuilding(tab, AT_AlterColumnType, rel, attnum, colName);
+
+	CommandCounterIncrement();
+
 	/*
 	 * Now scan for dependencies of this column on other things.  The only
 	 * things we should find are the dependency on the column datatype and
@@ -23902,3 +23927,233 @@ ATExecSplitPartition(List **wqueue, AlteredTableInfo *tab, Relation rel,
 	/* Restore the userid and security context. */
 	SetUserIdAndSecContext(save_userid, save_sec_context);
 }
+
+/*
+ * Record dependencies between objects contains whole-row Var references
+ * (indexes, CHECK constraints, etc.) and the relation, or report an
+ * error.
+*/
+static void
+RememberWholeRowDependent(AlteredTableInfo *tab, AlterTableType subtype,
+						  Relation rel, AttrNumber attnum,
+						  const ObjectAddress *depender,
+						  DependencyType behavior)
+{
+	if (subtype == AT_AlterColumnType)
+		ereport(ERROR,
+				errcode(ERRCODE_DATATYPE_MISMATCH),
+				errmsg("cannot alter table \"%s\" because %s uses its row type",
+					   RelationGetRelationName(rel),
+					   getObjectDescription(depender, false)),
+				errhint("You might need to drop %s first",
+						getObjectDescription(depender, false)));
+	else
+	{
+		ObjectAddress referenced;
+
+		ObjectAddressSubSet(referenced, RelationRelationId,
+							RelationGetRelid(rel), attnum);
+
+		recordDependencyOn(depender, &referenced, behavior);
+	}
+}
+
+/*
+ * Record dependencies between objects contains whole-row Var references
+ * (indexes, CHECK constraints, etc.) and the relation, or report an
+ * error.
+ *
+ * See also RememberAllDependentForRebuilding, which handles non-whole-row Var
+ * references.
+ *
+ * Currently used by ALTER COLUMN SET DATA TYPE and ALTER TABLE DROP COLUMN.
+  */
+static void
+RememberWholeRowDependentForRebuilding(AlteredTableInfo *tab, AlterTableType subtype,
+									   Relation rel, AttrNumber attnum, const char *colName)
+{
+	Node	   *expr = NULL;
+	ScanKeyData skey;
+	Relation	pg_index;
+	Relation	pg_constraint;
+	SysScanDesc indscan;
+	SysScanDesc conscan;
+	HeapTuple	constrTuple;
+	HeapTuple	indexTuple;
+	Datum		exprDatum;
+	char	   *exprString = NULL;
+	bool		isnull;
+	List	   *wholerow_idxoids = NIL;
+
+	Assert(subtype == AT_AlterColumnType || subtype == AT_DropColumn);
+
+	/*
+	 * Checking CHECK constraint with whole-row references, now.
+	 */
+	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))
+				{
+					ObjectAddress con_obj;
+
+					ObjectAddressSet(con_obj, ConstraintRelationId, conform->oid);
+
+					/*
+					 * The dependency between the CHECK constraint and its
+					 * relation is DEPENDENCY_AUTO
+					 */
+					RememberWholeRowDependent(tab, subtype, rel, attnum,
+											  &con_obj, DEPENDENCY_AUTO);
+				}
+			}
+		}
+		systable_endscan(conscan);
+		table_close(pg_constraint, AccessShareLock);
+	}
+
+	/*
+	 * Now checking index whole-row references. Prepare to scan pg_index for
+	 * entries having indrelid = this rel
+	 */
+	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 (list_member_oid(wholerow_idxoids, index->indexrelid))
+			continue;
+
+		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 whole-row reference then
+			 * remember it
+			 */
+			if (bms_is_member(InvalidAttrNumber - FirstLowInvalidHeapAttributeNumber,
+							  expr_attrs))
+			{
+				ObjectAddress idx_obj;
+
+				ObjectAddressSet(idx_obj, RelationRelationId,
+								 index->indexrelid);
+
+				wholerow_idxoids = lappend_oid(wholerow_idxoids,
+											   index->indexrelid);
+
+				/*
+				 * The index has a DEPENDENCY_AUTO relationship with its
+				 * relation
+				 */
+				RememberWholeRowDependent(tab, subtype, rel, attnum,
+										  &idx_obj, DEPENDENCY_AUTO);
+
+				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 whole-row reference
+			 * then remember it
+			 */
+			if (bms_is_member(InvalidAttrNumber - FirstLowInvalidHeapAttributeNumber,
+							  expr_attrs))
+			{
+				ObjectAddress idx_obj;
+
+				ObjectAddressSet(idx_obj, RelationRelationId, index->indexrelid);
+
+				wholerow_idxoids = lappend_oid(wholerow_idxoids,
+											   index->indexrelid);
+
+				/*
+				 * The index has a DEPENDENCY_AUTO relationship with its
+				 * relation
+				 */
+				RememberWholeRowDependent(tab, subtype, rel, attnum,
+										  &idx_obj, DEPENDENCY_AUTO);
+			}
+		}
+	}
+	systable_endscan(indscan);
+	table_close(pg_index, AccessShareLock);
+}
diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out
index e54fec7fb57..658dd421dba 100644
--- a/src/test/regress/expected/constraints.out
+++ b/src/test/regress/expected/constraints.out
@@ -254,6 +254,29 @@ ERROR:  system column "ctid" reference in check constraint is invalid
 LINE 3:       CHECK (NOT (is_capital AND ctid::text = 'sys_col_check...
                                          ^
 --
+-- Drop column should also drop all check constraints that contains whole-row references
+--
+-- Change column data type should fail since whole-row referenced check constraint still exists
+--
+-- No need to worry about partitioned tables, since the whole-row check constraint
+-- can not span multi relations
+CREATE TABLE wholerow_check_tbl (
+    city int,
+    state int,
+    CONSTRAINT cc0 CHECK (wholerow_check_tbl is null) NOT ENFORCED,
+    CONSTRAINT cc1 CHECK (wholerow_check_tbl is not null) NOT ENFORCED);
+ALTER TABLE wholerow_check_tbl ALTER COLUMN city SET DATA TYPE INT8; -- error
+ERROR:  cannot alter table "wholerow_check_tbl" because constraint cc0 on table wholerow_check_tbl uses its row type
+HINT:  You might need to drop constraint cc0 on table wholerow_check_tbl first
+ALTER TABLE wholerow_check_tbl DROP COLUMN city; -- ok
+\d wholerow_check_tbl
+         Table "public.wholerow_check_tbl"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ state  | integer |           |          | 
+
+DROP TABLE wholerow_check_tbl;
+--
 -- Check inheritance of defaults and constraints
 --
 CREATE TABLE INSERT_CHILD (cx INT default 42,
diff --git a/src/test/regress/expected/indexing.out b/src/test/regress/expected/indexing.out
index 929feda6fa3..424f7ea3f68 100644
--- a/src/test/regress/expected/indexing.out
+++ b/src/test/regress/expected/indexing.out
@@ -759,6 +759,38 @@ alter table idxpart2 drop column c;
  b      | integer |           |          | 
 
 drop table idxpart, idxpart2;
+-- Drop column should also drop all indexes that contains whole-row references
+-- Change column data type should fail if whole-row referenced indexes exists
+create table idxpart (a int, b int, c int) partition by range (a);
+create table idxpart1 partition of idxpart for values from (2000) to (3000);
+create index idxpart_idx1 on idxpart1((idxpart1 is not null));
+alter table idxpart alter column b set data type bigint; -- error
+ERROR:  cannot alter table "idxpart1" because index idxpart_idx1 uses its row type
+HINT:  You might need to drop index idxpart_idx1 first
+drop index idxpart_idx1;
+create index idxpart_idx2 on idxpart1(a) where idxpart1 is not null;
+alter table idxpart alter column c set data type bigint; -- error
+ERROR:  cannot alter table "idxpart1" because index idxpart_idx2 uses its row type
+HINT:  You might need to drop index idxpart_idx2 first
+alter table idxpart drop column c; -- ok
+\d idxpart
+        Partitioned table "public.idxpart"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ a      | integer |           |          | 
+ b      | integer |           |          | 
+Partition key: RANGE (a)
+Number of partitions: 1 (Use \d+ to list them.)
+
+\d idxpart1
+              Table "public.idxpart1"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ a      | integer |           |          | 
+ b      | integer |           |          | 
+Partition of: idxpart FOR VALUES FROM (2000) TO (3000)
+
+drop table idxpart;
 -- Verify that expression indexes inherit correctly
 create table idxpart (a int, b int) partition by range (a);
 create table idxpart1 (like idxpart);
diff --git a/src/test/regress/sql/constraints.sql b/src/test/regress/sql/constraints.sql
index dc133b124bb..2600c9f518d 100644
--- a/src/test/regress/sql/constraints.sql
+++ b/src/test/regress/sql/constraints.sql
@@ -165,6 +165,23 @@ CREATE TABLE SYS_COL_CHECK_TBL (city text, state text, is_capital bool,
                   altitude int,
 				  CHECK (NOT (is_capital AND ctid::text = 'sys_col_check_tbl')));
 
+--
+-- Drop column should also drop all check constraints that contains whole-row references
+--
+-- Change column data type should fail since whole-row referenced check constraint still exists
+--
+-- No need to worry about partitioned tables, since the whole-row check constraint
+-- can not span multi relations
+CREATE TABLE wholerow_check_tbl (
+    city int,
+    state int,
+    CONSTRAINT cc0 CHECK (wholerow_check_tbl is null) NOT ENFORCED,
+    CONSTRAINT cc1 CHECK (wholerow_check_tbl is not null) NOT ENFORCED);
+ALTER TABLE wholerow_check_tbl ALTER COLUMN city SET DATA TYPE INT8; -- error
+ALTER TABLE wholerow_check_tbl DROP COLUMN city; -- ok
+\d wholerow_check_tbl
+DROP TABLE wholerow_check_tbl;
+
 --
 -- Check inheritance of defaults and constraints
 --
diff --git a/src/test/regress/sql/indexing.sql b/src/test/regress/sql/indexing.sql
index 3d43af3323c..9bc5b83a5e6 100644
--- a/src/test/regress/sql/indexing.sql
+++ b/src/test/regress/sql/indexing.sql
@@ -354,6 +354,23 @@ alter table idxpart2 drop column c;
 \d idxpart2
 drop table idxpart, idxpart2;
 
+-- Drop column should also drop all indexes that contains whole-row references
+-- Change column data type should fail if whole-row referenced indexes exists
+create table idxpart (a int, b int, c int) partition by range (a);
+create table idxpart1 partition of idxpart for values from (2000) to (3000);
+
+create index idxpart_idx1 on idxpart1((idxpart1 is not null));
+alter table idxpart alter column b set data type bigint; -- error
+drop index idxpart_idx1;
+
+create index idxpart_idx2 on idxpart1(a) where idxpart1 is not null;
+alter table idxpart alter column c set data type bigint; -- error
+alter table idxpart drop column c; -- ok
+
+\d idxpart
+\d idxpart1
+drop table idxpart;
+
 -- Verify that expression indexes inherit correctly
 create table idxpart (a int, b int) partition by range (a);
 create table idxpart1 (like idxpart);
-- 
2.34.1