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

From 090a087da9b7fb072acd7e9683faf9ba2b5c76af Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
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/CACJufxGA6KVQy7DbHGLVw9s9KKmpGyZt5ME6C7kEfjDpr2wZCw@mail.gmail.com
---
 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