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

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

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          | 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