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