On Tue, May 26, 2026 at 3:47 PM Chao Li <[email protected]> wrote:
>
> >
> > I think this is a bug that we need to fix in 19 as well — I mean we should
> > reject the ALTER TABLE.
> >
> > --
> > Álvaro Herrera
>
> Thanks for your comment. Let me rework the patch.
>
Hi.
Here are the comments placed in ATExecAlterCheckConstrEnforceability I
came up with:
+ /*
+ * If the check constraint qual definitions match but their enforcement
+ * statuses conflict (parent enforced, child unenforced), it creates
+ * ambiguity around how insert operations should handle the mismatch.
+ * Therefore, we should avoid states where the parent check constraint is
+ * enforced while the child is not. We actually enforced this within
+ * MergeConstraintsIntoExisting and MergeWithExistingConstraint.
+ */
+ if (currcon->coninhcount > 0 && !recursing)
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+ errmsg("cannot alter inherited constraint \"%s\" of
relation \"%s\" enforciability",
+ NameStr(currcon->conname),
RelationGetRelationName(rel)));
--
jian
https://www.enterprisedb.com/
From 22c41ac02130f099ba13b0fe91f14ba68e29f9ac Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Tue, 26 May 2026 16:24:32 +0800
Subject: [PATCH v1 1/1] disallow alter enforciability of inherited check
constraint
If the check constraint qual definitions match but their enforcement statuses
conflict (parent enforced, child unenforced), it creates ambiguity around how
insert operations should handle the mismatch. Therefore, we should avoid states
where the parent check constraint is enforced while the child is not. We
actually enforced this within MergeConstraintsIntoExisting and
MergeWithExistingConstraint.
---
src/backend/commands/tablecmds.c | 14 ++++++++++++++
src/test/regress/expected/constraints.out | 9 +++++++--
src/test/regress/expected/inherit.out | 2 +-
src/test/regress/sql/constraints.sql | 2 ++
4 files changed, 24 insertions(+), 3 deletions(-)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 1e0bacf85fc..38250fd0573 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -12693,6 +12693,20 @@ ATExecAlterCheckConstrEnforceability(List **wqueue, ATAlterConstraint *cmdcon,
*/
rel = table_open(currcon->conrelid, NoLock);
+ /*
+ * If the check constraint qual definitions match but their enforcement
+ * statuses conflict (parent enforced, child unenforced), it creates
+ * ambiguity around how insert operations should handle the mismatch.
+ * Therefore, we should avoid states where the parent check constraint is
+ * enforced while the child is not. We actually enforced this within
+ * MergeConstraintsIntoExisting and MergeWithExistingConstraint.
+ */
+ if (currcon->coninhcount > 0 && !recursing)
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+ errmsg("cannot alter inherited constraint \"%s\" of relation \"%s\" enforciability",
+ NameStr(currcon->conname), RelationGetRelationName(rel)));
+
if (currcon->conenforced != cmdcon->is_enforced)
{
AlterConstrUpdateConstraintEntry(cmdcon, conrel, contuple);
diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out
index e54fec7fb57..89b33b66cf8 100644
--- a/src/test/regress/expected/constraints.out
+++ b/src/test/regress/expected/constraints.out
@@ -446,8 +446,13 @@ alter table parted_ch_2 alter constraint cc_2 enforced; --error
ERROR: check constraint "cc_2" of relation "parted_ch_2" is violated by some row
delete from parted_ch where a = 16;
alter table parted_ch_2 alter constraint cc_2 enforced;
+-- error, cannot alter inherited check constraint
alter table parted_ch_2 alter constraint cc not enforced;
+ERROR: cannot alter inherited constraint "cc" of relation "parted_ch_2" enforciability
+alter table only parted_ch_2 alter constraint cc not enforced; -- error
+ERROR: cannot alter inherited constraint "cc" of relation "parted_ch_2" enforciability
alter table parted_ch_2 alter constraint cc_1 not enforced;
+ERROR: cannot alter inherited constraint "cc_1" of relation "parted_ch_2" enforciability
alter table parted_ch_2 alter constraint cc_2 not enforced;
--check these CHECK constraint status again
select * from check_constraint_status;
@@ -457,12 +462,12 @@ select * from check_constraint_status;
cc | parted_ch_1 | t | t
cc | parted_ch_11 | t | t
cc | parted_ch_12 | t | t
- cc | parted_ch_2 | f | f
+ cc | parted_ch_2 | t | t
cc_1 | parted_ch | t | t
cc_1 | parted_ch_1 | t | t
cc_1 | parted_ch_11 | t | t
cc_1 | parted_ch_12 | t | t
- cc_1 | parted_ch_2 | f | f
+ cc_1 | parted_ch_2 | t | t
cc_2 | parted_ch_2 | f | f
(11 rows)
diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out
index 3d8e8d8afd2..66d08c4d746 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -1458,7 +1458,7 @@ alter table p1 alter constraint inh_check_constraint3 enforced; --error
ERROR: check constraint "inh_check_constraint3" of relation "p1_c1" is violated by some row
delete from only p1_c1 where f1 = -2;
alter table p1_c1 alter constraint inh_check_constraint3 enforced; --error
-ERROR: check constraint "inh_check_constraint3" of relation "p1_c3" is violated by some row
+ERROR: cannot alter inherited constraint "inh_check_constraint3" of relation "p1_c1" enforciability
delete from only p1_c3 where f1 = -3;
alter table p1 alter constraint inh_check_constraint3 enforced; --ok
alter table p1 alter constraint inh_check_constraint3 not enforced; --ok
diff --git a/src/test/regress/sql/constraints.sql b/src/test/regress/sql/constraints.sql
index dc133b124bb..f60263fa1f5 100644
--- a/src/test/regress/sql/constraints.sql
+++ b/src/test/regress/sql/constraints.sql
@@ -309,7 +309,9 @@ select * from check_constraint_status;
alter table parted_ch_2 alter constraint cc_2 enforced; --error
delete from parted_ch where a = 16;
alter table parted_ch_2 alter constraint cc_2 enforced;
+-- error, cannot alter inherited check constraint
alter table parted_ch_2 alter constraint cc not enforced;
+alter table only parted_ch_2 alter constraint cc not enforced; -- error
alter table parted_ch_2 alter constraint cc_1 not enforced;
alter table parted_ch_2 alter constraint cc_2 not enforced;
base-commit: d40aed554227e5f8203798fda25b936a264638ec
--
2.34.1
