v5-0002-disallow-ALTER-COLUMN-SET-DATA-TYPE-when-wholerow-referenced-cons.patch

From 74ce2ccae810826bb080f014a546b0b011b86171 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
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

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 "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/CACJufxGA6KVQy7DbHGLVw9s9KKmpGyZt5ME6C7kEfjDpr2wZCw@mail.gmail.com
---
 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(+)

diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index a3d1fe658c6..9bf5d6bc2b6 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, 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;
 
 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 40512a8853a..4b683c679a8 100644
--- a/src/test/regress/expected/indexing.out
+++ b/src/test/regress/expected/indexing.out
@@ -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;
 
 -- Verify that expression indexes inherit correctly
-- 
2.34.1