hi.
CREATE TABLE ts (a int, c int, b int
constraint cc check((ts = ROW(1,1,1))),
constraint cc1 check((ts.a = 1)));
CREATE INDEX tsi on ts (a) where a = 1;
CREATE INDEX tsi2 on ts ((a is null));
CREATE INDEX tsi3 on ts ((ts is null));
CREATE INDEX tsi4 on ts (b) where ts is not null;
in the master, ``ALTER TABLE ts DROP COLUMN a;``
will not drop constraint cc, index tsi3, tsi4;
with the attached patch,
``ALTER TABLE ts DROP COLUMN a;``
will drop above all indexes on the table "ts" and also remove the
constraints "cc" and "cc1".
as per the documentation[1], quote:
"""
DROP COLUMN [ IF EXISTS ]
This form drops a column from a table. Indexes and table constraints involving
the column will be automatically dropped as well.
"""
so I think it's expected behavior to drop the entire
whole-row referenced indexes and constraints.
[1]
https://www.postgresql.org/docs/devel/sql-altertable.html#SQL-ALTERTABLE-DESC-DROP-COLUMN
From 82f795b26e9438990680f0c56ff82853504e80eb Mon Sep 17 00:00:00 2001
From: jian he <jian.universal...@gmail.com>
Date: Mon, 8 Sep 2025 02:06:18 +0800
Subject: [PATCH v1 1/1] let ALTER TABLE DROP COLUMN drop wholerow referenced
object
CREATE TABLE ts (a int, c int, b int
constraint cc check((ts = ROW(1,1,1))),
constraint cc1 check((ts.a = 1)));
CREATE INDEX tsi on ts (a) where a = 1;
CREATE INDEX tsi2 on ts ((a is null));
CREATE INDEX tsi3 on ts ((ts is null));
CREATE INDEX tsi4 on ts (b) where ts is not null;
ALTER TABLE ts DROP COLUMN a;
will drop above all indexes on the table ts and also remove the constraints cc
and cc1.
discussion: https://postgr.es/m/
---
src/backend/commands/tablecmds.c | 122 ++++++++++++++++++++++
src/test/regress/expected/constraints.out | 17 +++
src/test/regress/expected/indexing.out | 25 +++++
src/test/regress/sql/constraints.sql | 11 ++
src/test/regress/sql/indexing.sql | 9 ++
5 files changed, 184 insertions(+)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 082a3575d62..94224ce2c8d 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -9257,6 +9257,9 @@ ATExecDropColumn(List **wqueue, Relation rel, const char *colName,
List *children;
ObjectAddress object;
bool is_expr;
+ Node *expr;
+ List *indexlist = NIL;
+ TupleConstr *constr = RelationGetDescr(rel)->constr;
/* At top level, permission check was done in ATPrepCmd, else do it */
if (recursing)
@@ -9329,6 +9332,125 @@ ATExecDropColumn(List **wqueue, Relation rel, const char *colName,
ReleaseSysCache(tuple);
+ /* also drop the check constraint that references whole-row */
+ if (constr && constr->num_check > 0)
+ {
+ ConstrCheck *check = constr->check;
+
+ for (int i = 0; i < constr->num_check; i++)
+ {
+ Bitmapset *expr_attrs = NULL;
+ char *constr_name = check[i].ccname;
+
+ expr = stringToNode(check[i].ccbin);
+
+ /* Find all attributes referenced */
+ pull_varattnos(expr, 1, &expr_attrs);
+
+ if (bms_is_member(0 - FirstLowInvalidHeapAttributeNumber, expr_attrs))
+ {
+ Relation conDesc;
+ SysScanDesc conscan;
+ ScanKeyData skey[3];
+ HeapTuple contuple;
+
+ /* Search for a pg_constraint entry with same name and relation */
+ conDesc = table_open(ConstraintRelationId, AccessShareLock);
+
+ ScanKeyInit(&skey[0],
+ Anum_pg_constraint_conrelid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(RelationGetRelid(rel)));
+ ScanKeyInit(&skey[1],
+ Anum_pg_constraint_contypid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(InvalidOid));
+ ScanKeyInit(&skey[2],
+ Anum_pg_constraint_conname,
+ BTEqualStrategyNumber, F_NAMEEQ,
+ CStringGetDatum(constr_name));
+
+ conscan = systable_beginscan(conDesc, ConstraintRelidTypidNameIndexId, true,
+ NULL, 3, skey);
+
+ if (!HeapTupleIsValid(contuple = systable_getnext(conscan)))
+ elog(ERROR, "constraint \"%s\" of relation \"%s\" does not exist",
+ constr_name, RelationGetRelationName(rel));
+
+ /* Add object to delete */
+ object.classId = ConstraintRelationId;
+ object.objectId = ((Form_pg_constraint) GETSTRUCT(contuple))->oid;
+ object.objectSubId = 0;
+ add_exact_object_address(&object, addrs);
+
+ systable_endscan(conscan);
+ table_close(conDesc, AccessShareLock);
+ }
+ }
+ }
+
+ /* also drop the indexes that references whole-row */
+ indexlist = RelationGetIndexList(rel);
+ foreach_oid(indexoid, indexlist)
+ {
+ HeapTuple indexTuple;
+ Form_pg_index indexStruct;
+ Node *node;
+
+ indexTuple = SearchSysCache1(INDEXRELID, ObjectIdGetDatum(indexoid));
+ if (!HeapTupleIsValid(indexTuple))
+ elog(ERROR, "cache lookup failed for index %u", indexoid);
+ indexStruct = (Form_pg_index) GETSTRUCT(indexTuple);
+
+ if (!heap_attisnull(indexTuple, Anum_pg_index_indpred, NULL))
+ {
+ Datum predDatum;
+ char *predString;
+ Bitmapset *expr_attrs = NULL;
+
+ /* Convert text string to node tree */
+ predDatum = SysCacheGetAttrNotNull(INDEXRELID, indexTuple,
+ Anum_pg_index_indpred);
+ predString = TextDatumGetCString(predDatum);
+ node = (Node *) stringToNode(predString);
+ pfree(predString);
+
+ pull_varattnos(node, 1, &expr_attrs);
+ if (bms_is_member(0 - FirstLowInvalidHeapAttributeNumber, expr_attrs))
+ {
+ object.classId = RelationRelationId;
+ object.objectId = indexStruct->indexrelid;
+ object.objectSubId = 0;
+ add_exact_object_address(&object, addrs);
+ }
+ }
+ else if (!heap_attisnull(indexTuple, Anum_pg_index_indexprs, NULL))
+ {
+ Datum exprDatum;
+ char *exprString;
+ Bitmapset *expr_attrs = NULL;
+
+ /* Convert text string to node tree */
+ exprDatum = SysCacheGetAttrNotNull(INDEXRELID, indexTuple,
+ Anum_pg_index_indexprs);
+ exprString = TextDatumGetCString(exprDatum);
+ node = (Node *) stringToNode(exprString);
+ pfree(exprString);
+
+ pull_varattnos(node, 1, &expr_attrs);
+
+ /* Add object to delete */
+ if (bms_is_member(0 - FirstLowInvalidHeapAttributeNumber, expr_attrs))
+ {
+ object.classId = RelationRelationId;
+ object.objectId = indexStruct->indexrelid;
+ object.objectSubId = 0;
+ add_exact_object_address(&object, addrs);
+ }
+ }
+ ReleaseSysCache(indexTuple);
+ }
+
/*
* Propagate to children as appropriate. Unlike most other ALTER
* routines, we have to do this one level of recursion at a time; we can't
diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out
index 3590d3274f0..ce2fb02971f 100644
--- a/src/test/regress/expected/constraints.out
+++ b/src/test/regress/expected/constraints.out
@@ -254,6 +254,23 @@ ERROR: system column "ctid" reference in check constraint is invalid
LINE 3: CHECK (NOT (is_capital AND ctid::text = 'sys_col_check...
^
--
+-- Drop column also drop the associated Check constraints and whole-row referenced check constraint
+--
+CREATE TABLE DROP_COL_CHECK_TBL (
+ city text, state text, is_capital bool, altitude int,
+ CONSTRAINT cc CHECK (city is not null),
+ CONSTRAINT cc1 CHECK (DROP_COL_CHECK_TBL is not null));
+ALTER TABLE DROP_COL_CHECK_TBL DROP COLUMN city;
+\d DROP_COL_CHECK_TBL
+ Table "public.drop_col_check_tbl"
+ Column | Type | Collation | Nullable | Default
+------------+---------+-----------+----------+---------
+ state | text | | |
+ is_capital | boolean | | |
+ altitude | integer | | |
+
+DROP TABLE DROP_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 4d29fb85293..ec32543c1e4 100644
--- a/src/test/regress/expected/indexing.out
+++ b/src/test/regress/expected/indexing.out
@@ -654,6 +654,31 @@ alter table idxpart2 drop column c;
b | integer | | |
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));
+create index on idxpart(a) where idxpart is not null;
+\d idxpart
+ Table "public.idxpart"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+ b | integer | | |
+ c | integer | | |
+Indexes:
+ "idxpart_a_idx" btree (a) WHERE idxpart.* IS NOT NULL
+ "idxpart_c_idx" btree (c)
+ "idxpart_expr_idx" btree ((idxpart.* IS NOT NULL))
+
+alter table idxpart drop column c;
+\d idxpart
+ Table "public.idxpart"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+ b | integer | | |
+
+drop table idxpart;
-- Verify that expression indexes inherit correctly
create table idxpart (a int, b int) partition by range (a);
create table idxpart1 (like idxpart);
diff --git a/src/test/regress/sql/constraints.sql b/src/test/regress/sql/constraints.sql
index 1f6dc8fd69f..545f8fa17a3 100644
--- a/src/test/regress/sql/constraints.sql
+++ b/src/test/regress/sql/constraints.sql
@@ -165,6 +165,17 @@ CREATE TABLE SYS_COL_CHECK_TBL (city text, state text, is_capital bool,
altitude int,
CHECK (NOT (is_capital AND ctid::text = 'sys_col_check_tbl')));
+--
+-- Drop column also drop the associated Check constraints and whole-row referenced check constraint
+--
+CREATE TABLE DROP_COL_CHECK_TBL (
+ city text, state text, is_capital bool, altitude int,
+ CONSTRAINT cc CHECK (city is not null),
+ CONSTRAINT cc1 CHECK (DROP_COL_CHECK_TBL is not null));
+ALTER TABLE DROP_COL_CHECK_TBL DROP COLUMN city;
+\d DROP_COL_CHECK_TBL
+DROP TABLE DROP_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 b5cb01c2d70..825625b01b6 100644
--- a/src/test/regress/sql/indexing.sql
+++ b/src/test/regress/sql/indexing.sql
@@ -295,6 +295,15 @@ alter table idxpart2 drop column c;
\d idxpart2
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));
+create index on idxpart(a) where idxpart is not null;
+\d idxpart
+alter table idxpart drop column c;
+\d idxpart
+drop table idxpart;
+
-- Verify that expression indexes inherit correctly
create table idxpart (a int, b int) partition by range (a);
create table idxpart1 (like idxpart);
--
2.34.1
