From 07ac16212729fb71b0f3fa8557688e0788477625 Mon Sep 17 00:00:00 2001 From: jian he 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