Subject: Fix bug of CHECK constraint enforceability recursion

From: Chao Li <[email-masked]>
Date: Tue, 26 May 2026 11:51:11 +0800

Hi,

I just tested “Add support for altering CHECK constraint enforceability” and found an issue where recursion is not handled properly.

Here is a repro with inheritance tables:
```
evantest=# create table p(a int constraint ck check (a > 0) enforced);
CREATE TABLE
evantest=# create table c() inherits (p);
CREATE TABLE
evantest=# alter table c alter constraint ck not enforced;
ALTER TABLE
evantest=# insert into c values (-1);
INSERT 0 1
evantest=# alter table p alter constraint ck enforced;
ALTER TABLE
evantest=# insert into c values (-2);
INSERT 0 1
evantest=# select * from p;
 a
----
 -1
 -2
(2 rows)
```

In this repro, the constraint on parent table p is already ENFORCED, but the constraint on child table c was altered to NOT ENFORCED. So when altering p to ENFORCED again, it didn't recurse to c.

The same problem can happen with partitioned tables as well:
```
evantest=# create table p (a int, constraint ck check (a > 0) enforced) partition by range (a);
CREATE TABLE
evantest=# create table p1 partition of p for values from (-100) to (100);
CREATE TABLE
evantest=# insert into p1 values (-1);
ERROR:  new row for relation "p1" violates check constraint "ck"
DETAIL:  Failing row contains (-1).
evantest=# alter table p1 alter constraint ck not enforced;
ALTER TABLE
evantest=# insert into p1 values (-1);
INSERT 0 1
evantest=# alter table p alter constraint ck enforced;
ALTER TABLE
evantest=# insert into p1 values (-2);
INSERT 0 1
evantest=#
evantest=# select * from p;
 a
----
 -1
 -2
(2 rows)
```

For the solution, I think we should always recurse to descendant tables unless the constraint is NO INHERIT, because both partitioned tables and inheritance children can currently be altered to have different enforceability. So we cannot rely on whether the parent constraint itself was changed.

See the attached patch for details. I also added regress test cases for the fix.

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/

---

From: Chao Li <[email-masked]>
Date: Tue, 26 May 2026 13:48:02 +0800

> On May 26, 2026, at 11:51, Chao Li <[email-masked]> wrote:
> 
> Hi,
> 
> <v1-0001-Fix-CHECK-constraint-enforceability-recursion.patch>

Merged the doc change from [1] into this thread as they are for the same feature.

[1] https://postgr.es/m/[email-masked]

Best regards,

---

From: jian he <[email-masked]>
Date: Tue, 26 May 2026 14:05:08 +0800

> The same problem can happen with partitioned tables as well:

Hi.

In MergeConstraintsIntoExisting, we have:
/*
 * A NOT ENFORCED child constraint cannot be merged with an
 * ENFORCED parent constraint. However, the reverse is allowed,
 * where the child constraint is ENFORCED.
 */
if (parent_con->conenforced && !child_con->conenforced)
    ereport(ERROR,
            (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
             errmsg("constraint \"%s\" conflicts with NOT ENFORCED
constraint on child table \"%s\"",
                NameStr(child_con->conname),
RelationGetRelationName(child_rel))));

MergeWithExistingConstraint, we have comments like:
        /*
         * If the child constraint is required to be enforced while the parent
         * constraint is not, this should be allowed by marking the child
         * constraint as enforced. In the reverse case, an error would have
         * already been thrown before reaching this point.
         */

So other commands (CREATE TABLE, ALTER TABLE ATTACH PARTITION) do not expect a
state where the parent constraint is enforced but the child constraint is not.
We can now reach this state via ALTER TABLE ALTER CONSTRAINT.

We don't need to worry about Foreign Key Constraints because the
foreign key constraint's conparentid is valid, therefore we cannot
directly alter a partition's FK constraint.
StoreRelCheck->CreateConstraintEntry comments ``/* no parent
constraint */`` means that each CHECK constraint is on its own.

Overall, i tend to think that we should reject ALTER TABLE ALTER
CONSTRAINT if it
would result in the parent constraint being enforced while the child constraint
is not enforced.

--
jian
https://www.enterprisedb.com/

---

From: Chao Li <[email-masked]>
Date: Tue, 26 May 2026 14:27:34 +0800

> On May 26, 2026, at 14:05, jian he <[email-masked]> wrote:
> 

I am not against the idea of "rejecting ALTER TABLE ALTER CONSTRAINT if it would result in the parent constraint being enforced while the child constrain is not enforced", but I’m afraid it’s too late for PG19. So, I guess we still need to fix the issue for 19, right?

Best regards,

---

From: Álvaro Herrera <[email-masked]>
Date: Tue, 26 May 2026 09:32:39 +0200

>> would result in the parent constraint being enforced while the child constraint

Yeah.

> I am not against the idea of "rejecting ALTER TABLE ALTER CONSTRAINT if 
> it would result in the parent constraint being enforced while the child 
> constrain is not enforced", but I’m afraid it’s too late for PG19. So, 
> I guess we still need to fix the issue for 19, right?

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

---

From: Chao Li <[email-masked]>
Date: Tue, 26 May 2026 15:44:01 +0800

> On May 26, 2026, at 14:27, Chao Li <[email-masked]> wrote:
> 
> 
> 

I thought this over, and I changed my mind.

The same rule should apply to both partitioned tables and regular inheritance:

* parent CHECK enforced + child CHECK not enforced = reject
* parent CHECK not enforced + child CHECK enforced = allow

That matches the existing merge/attach behavior. Also, this invariant could not be broken through normal SQL in PG18, because PG18 does not support ALTER TABLE ... ALTER CONSTRAINT ... [NOT] ENFORCED for CHECK constraints. So we should not introduce a new way to break it in PG19.

I will rework the patch forwards the “reject” direction.

Best regards,

---

From: Chao Li <[email-masked]>
Date: Tue, 26 May 2026 15:46:46 +0800

> On May 26, 2026, at 15:32, Álvaro Herrera <[email-masked]> wrote:
> 

Thanks for your comment. Let me rework the patch.

Best regards,

---

From: jian he <[email-masked]>
Date: Tue, 26 May 2026 16:32:29 +0800

> 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-masked]>
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

---

From: Chao Li <[email-masked]>
Date: Wed, 27 May 2026 14:20:21 +0800

> On May 26, 2026, at 16:32, jian he <[email-masked]> wrote:
> 
> <v1-0001-disallow-alter-enforciability-of-inherited-check-constraint.patch>

Hi Jian,

Thanks for your help. Your implementation is simple and clever:
```

Basically, it disallows all enforceability changes on inherited constraints (currcon->coninhcount > 0) at the recursion root (!recursing), or in other words, it disallows the operation on any child table.

But I see several problems with this implementation:

1. As you pointed out earlier, when a parent is ENFORCED, changing a child from ENFORCED to NOT ENFORCED should not be allowed. But when a parent is NOT ENFORCED, changing a child from NOT ENFORCED to ENFORCED should be allowed. The existing phase 3 checking also proves that.

2. Suppose a parent table is NOT ENFORCED, and a user changes a child from NOT ENFORCED to ENFORCED, which is allowed. Later, if the user wants to change the child back from ENFORCED to NOT ENFORCED, that should also be allowed. But with your v1 patch, the user would have to do the change through the parent table, which I think hurts the user experience.

3. Suppose a child table is already ENFORCED, and a user issues a command to change it to ENFORCED again, which is actually a no-op. PostgreSQL usually allows this kind of no-op, but with your v1 patch, this no-op would get an error as well, which I think also hurts the user experience.

4. It cannot handle some complicated inheritance hierarchies. For example, the following test passes with your v1:
```
evantest=# CREATE TABLE p1 (a int CONSTRAINT c CHECK (a > 0) ENFORCED);
CREATE TABLE
evantest=# CREATE TABLE p2 (a int CONSTRAINT c CHECK (a > 0) ENFORCED);
CREATE TABLE
evantest=#
evantest=# CREATE TABLE ch () INHERITS (p1, p2);
NOTICE:  merging multiple inherited definitions of column "a"
CREATE TABLE
evantest=# ALTER TABLE p1 ALTER CONSTRAINT c NOT ENFORCED;
ALTER TABLE
```

I originally thought this should fail, but it now changes ch.c to NOT ENFORCED, so it breaks the rule because its parent p2 is still ENFORCED:
```
evantest=# SELECT conrelid::regclass, conname, conenforced, coninhcount, conislocal
evantest-# FROM pg_constraint WHERE conname = 'c';
 conrelid | conname | conenforced | coninhcount | conislocal
----------+---------+-------------+-------------+------------
 p1       | c       | f           |           0 | t
 p2       | c       | t           |           0 | t
 ch       | c       | f           |           2 | f
(3 rows)
```

Then I realized that the initial CREATE TABLE case passes:
```
evantest=# CREATE TABLE p1 (a int CONSTRAINT c CHECK (a > 0) NOT ENFORCED);
CREATE TABLE
evantest=# CREATE TABLE p2 (a int CONSTRAINT c CHECK (a > 0) ENFORCED);
CREATE TABLE
evantest=# CREATE TABLE ch () INHERITS (p1, p2);
NOTICE:  merging multiple inherited definitions of column "a"
CREATE TABLE
evantest=# SELECT conrelid::regclass, conname, conenforced, coninhcount, conislocal
evantest-# FROM pg_constraint WHERE conname = ‘c';
 conrelid | conname | conenforced | coninhcount | conislocal
----------+---------+-------------+-------------+------------
 ch       | c       | t           |           2 | f
 p1       | c       | f           |           0 | t
 p2       | c       | t           |           0 | t
(3 rows)
```

When the two parents have different enforceability, the stricter one is applied to the child. So I think the test above in item 4 should also perform similar merge logic rather than fail. This seems to uncover a new issue in the original feature patch.

For the fix, my design is:

* Directly reject changing an inherited child CHECK constraint to NOT ENFORCED if an equivalent parent constraint remains ENFORCED.
* Changing a child to ENFORCED is allowed.
* During recursing, if a child also inherits an equivalent ENFORCED constraint from another parent outside the current ALTER, the child keeps the stricter ENFORCED state.

Please see my implementation in the attached v2 patch.

Best regards,

---

From: jian he <[email-masked]>
Date: Thu, 28 May 2026 10:31:35 +0800

> Please see my implementation in the attached v2 patch.

CREATE TABLE p1 (a int CONSTRAINT c CHECK (a > 0) ENFORCED);
CREATE TABLE p2 (a int CONSTRAINT c CHECK (a > 0) ENFORCED);
CREATE TABLE ch () INHERITS (p1, p2);
ALTER TABLE p1 ALTER CONSTRAINT c NOT ENFORCED;

The v2 patch marks check constraint c on table ch as ENFORCED, which
seems to contradict the documentation's wording:
https://www.postgresql.org/docs/devel/ddl-inherit.html
<<>>
ALTER TABLE will propagate any changes in column data definitions and check
constraints down the inheritance hierarchy. Again, dropping columns that are
depended on by other tables is only possible when using the CASCADE option.
ALTER TABLE follows the same rules for duplicate column merging and rejection
that apply during CREATE TABLE
<<>>

The wording (https://www.postgresql.org/docs/devel/ddl-inherit.html)
below also discourages directly altering check constraints on child tables.
<<>>
A parent table cannot be dropped while any of its children remain. Neither can
columns or check constraints of child tables be dropped or altered if they are
inherited from any parent tables. If you wish to remove a table and all of its
descendants, one easy way is to drop the parent table with the CASCADE option
(see Section 5.17).
<<>>

---

From: Álvaro Herrera <[email-masked]>
Date: Thu, 28 May 2026 10:55:45 +0200

> seems to contradict the documentation's wording:

I think what v2 is doing in this case is correct.  The child's
constraint must preserve whatever the strictest of the inherited
constraints status is.  In this case, because the constraint on p2
remains ENFORCED, then it must remain ENFORCED in the child as well.
Changing it to NOT ENFORCED after the ALTER TABLE would be wrong.

> that apply during CREATE TABLE

I think this text is a bit vague in that it isn't really considering
multiple inheritance -- it appears to be written with the perspective of
each child table having a single parent.

It may be a bit obsolete also; it talks about "check constraints" but we
also allow not-null constraints to have these kind of properties as
well (which we didn't when this was written).

> descendants, one easy way is to drop the parent table with the CASCADE option

Hmm, I think this text is borderline obsolete, in the sense that we know
allow changing some properties of some constraints in child tables.
I'm not really sure to what extent it is useful to make it more explicit
about that.

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/
"You're _really_ hosed if the person doing the hiring doesn't understand
relational systems: you end up with a whole raft of programmers, none of
whom has had a Date with the clue stick."              (Andrew Sullivan)
https://postgr.es/m/[email-masked]

---

From: Chao Li <[email-masked]>
Date: Fri, 29 May 2026 06:56:22 +0800

> On May 28, 2026, at 16:55, Álvaro Herrera <[email-masked]> wrote:
> 

Thank Jian for pointing out the doc.

Thank Álvaro for guiding the direction. 

I just tried to update the doc in v3-0002, while 0001 is unchanged.

Best regards,

---

From: jian he <[email-masked]>
Date: Mon, 1 Jun 2026 23:51:54 +0800

Hi.

I did some refactoring based on your v3, I didn't change your comments.

I don't think ATGetEquivalentCheckConstraintOid is necessary.
ATCheckCheckConstrHasEnforcedParent,
AlterCheckConstrEnforceabilityRecurse is enough for this context.

+ changing_conids = lappend_oid(list_copy(changing_conids),
+  currcon->oid);
Refactor changing_conids to List ** to allow direct modification,
aligning with existing code conventions.

CREATE TABLE ... PARTITION OF automatically copies the parent's status, and
ALTER TABLE ... ATTACH PARTITION already rejects cases where the parent is
enforced but the child is not. If we also reject directly altering a partition's
constraint enforcement status, then we need no longer worry about cases where
parent being enforced while a child is not. Therefore, invoking
ATCheckCheckConstrHasEnforcedParent just once for table partitioning is safe.

--
jian
https://www.enterprisedb.com/
From 53604bd9b5f9164000ad4b36e859f3df7b606c89 Mon Sep 17 00:00:00 2001
From: jian he <[email-masked]>
Date: Mon, 1 Jun 2026 21:00:54 +0800
Subject: [PATCH v4 1/1] Prevent inherited CHECK constraints from being
 weakened

Disallow marking an inherited CHECK constraint as NOT ENFORCED when an
equivalent parent constraint remains ENFORCED. This prevents ALTER
CONSTRAINT from producing a child constraint that is weaker than one of
its inherited parent definitions.

When recursively altering a CHECK constraint to NOT ENFORCED, collect the
equivalent constraints in the affected inheritance subtree and ignore
those parent constraints while checking descendants. If a descendant also
inherits an equivalent ENFORCED constraint from a parent outside the
current ALTER, keep the descendant ENFORCED by merging to the stricter
state.

Add regression coverage for direct child ALTER, ONLY ALTER, mixed-parent
inheritance, and a common-ancestor diamond where all equivalent inherited
constraints can be changed together.

Author: Chao Li <[email-masked]>
Reviewed-by:
Discussion: https://postgr.es/m/[email-masked]
---
 src/backend/commands/tablecmds.c          | 185 +++++++++++++++++++++-
 src/test/regress/expected/constraints.out |  12 +-
 src/test/regress/expected/inherit.out     |  56 +++++++
 src/test/regress/sql/constraints.sql      |   5 +-
 src/test/regress/sql/inherit.sql          |  34 ++++
 5 files changed, 282 insertions(+), 10 deletions(-)

diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index a1845240a98..75ade053b38 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -437,6 +437,7 @@ static bool ATExecAlterFKConstrEnforceability(List **wqueue, ATAlterConstraint *
 static bool ATExecAlterCheckConstrEnforceability(List **wqueue, ATAlterConstraint *cmdcon,
 												 Relation conrel, HeapTuple contuple,
 												 bool recurse, bool recursing,
+												 List **changing_conids,
 												 LOCKMODE lockmode);
 static bool ATExecAlterConstrDeferrability(List **wqueue, ATAlterConstraint *cmdcon,
 										   Relation conrel, Relation tgrel, Relation rel,
@@ -459,6 +460,7 @@ static void AlterFKConstrEnforceabilityRecurse(List **wqueue, ATAlterConstraint
 static void AlterCheckConstrEnforceabilityRecurse(List **wqueue, ATAlterConstraint *cmdcon,
 												  Relation conrel, Oid conrelid,
 												  bool recurse, bool recursing,
+												  List **changing_conids,
 												  LOCKMODE lockmode);
 static void AlterConstrDeferrabilityRecurse(List **wqueue, ATAlterConstraint *cmdcon,
 											Relation conrel, Relation tgrel, Relation rel,
@@ -466,6 +468,10 @@ static void AlterConstrDeferrabilityRecurse(List **wqueue, ATAlterConstraint *cm
 											List **otherrelids, LOCKMODE lockmode);
 static void AlterConstrUpdateConstraintEntry(ATAlterConstraint *cmdcon, Relation conrel,
 											 HeapTuple contuple);
+static bool ATCheckCheckConstrHasEnforcedParent(Relation conrel, Relation rel,
+												HeapTuple contuple,
+												List **changing_conids,
+												Oid *enforced_parentoid);
 static ObjectAddress ATExecValidateConstraint(List **wqueue,
 											  Relation rel, char *constrName,
 											  bool recurse, bool recursing, LOCKMODE lockmode);
@@ -477,6 +483,7 @@ static void QueueCheckConstraintValidation(List **wqueue, Relation conrel, Relat
 static void QueueNNConstraintValidation(List **wqueue, Relation conrel, Relation rel,
 										HeapTuple contuple, bool recurse, bool recursing,
 										LOCKMODE lockmode);
+static bool constraints_equivalent(HeapTuple a, HeapTuple b, TupleDesc tupleDesc);
 static int	transformColumnNameList(Oid relId, List *colList,
 									int16 *attnums, Oid *atttypids, Oid *attcollids);
 static int	transformFkeyGetPrimaryKey(Relation pkrel, Oid *indexOid,
@@ -12482,9 +12489,13 @@ ATExecAlterConstraintInternal(List **wqueue, ATAlterConstraint *cmdcon,
 														InvalidOid, InvalidOid,
 														InvalidOid, InvalidOid);
 		else if (currcon->contype == CONSTRAINT_CHECK)
+		{
+			List	   *changing_conids = NIL;
+
 			changed = ATExecAlterCheckConstrEnforceability(wqueue, cmdcon, conrel,
 														   contuple, recurse, false,
-														   lockmode);
+														   &changing_conids, lockmode);
+		}
 	}
 	else if (cmdcon->alterDeferrability &&
 			 ATExecAlterConstrDeferrability(wqueue, cmdcon, conrel, tgrel, rel,
@@ -12671,7 +12682,9 @@ ATExecAlterFKConstrEnforceability(List **wqueue, ATAlterConstraint *cmdcon,
 static bool
 ATExecAlterCheckConstrEnforceability(List **wqueue, ATAlterConstraint *cmdcon,
 									 Relation conrel, HeapTuple contuple,
-									 bool recurse, bool recursing, LOCKMODE lockmode)
+									 bool recurse, bool recursing,
+									 List **changing_conids,
+									 LOCKMODE lockmode)
 {
 	Form_pg_constraint currcon;
 	Relation	rel;
@@ -12693,6 +12706,66 @@ ATExecAlterCheckConstrEnforceability(List **wqueue, ATAlterConstraint *cmdcon,
 	 */
 	rel = table_open(currcon->conrelid, NoLock);
 
+	/*
+	 * When setting a constraint to NOT ENFORCED, check whether any matching
+	 * parent constraint remains ENFORCED and is not part of this ALTER.
+	 *
+	 * For a direct ALTER of an inherited constraint, reject the command,
+	 * because the child cannot be weakened while its parent remains enforced.
+	 *
+	 * During recursion, another parent outside this ALTER may still enforce
+	 * the same constraint. In that case, keep the child constraint ENFORCED
+	 * so that its merged enforceability still reflects the remaining enforced
+	 * parent.
+	 */
+	if (!cmdcon->is_enforced)
+	{
+		Oid			enforced_parentoid = InvalidOid;
+
+		bool		enforced_parent_constr = false;
+
+		/*
+		 * For partitioned tables, a one-time verification is needed to ensure
+		 * that a partition's constraint cannot be marked as unenforced if the
+		 * parent's constraint remains enforced.
+		 *
+		 * For traditional table inheritance, if a constraint is unenforced
+		 * but its parent constraint is enforced, both this constraint and its
+		 * descendants must be marked as enforced.
+		 */
+		if ((rel->rd_rel->relispartition && !recursing) ||
+			(!rel->rd_rel->relispartition && rel->rd_rel->relkind != RELKIND_PARTITIONED_TABLE))
+		{
+			enforced_parent_constr = ATCheckCheckConstrHasEnforcedParent(conrel, rel, contuple,
+																		 changing_conids,
+																		 &enforced_parentoid);
+
+			if (enforced_parent_constr)
+				cmdcon->is_enforced = true;
+		}
+
+		if (enforced_parent_constr && !recursing)
+			ereport(ERROR,
+					errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+					errmsg("cannot mark inherited constraint \"%s\" as NOT ENFORCED because "
+						   "matching constraint on parent table \"%s\" is ENFORCED",
+						   NameStr(currcon->conname),
+						   get_rel_name(enforced_parentoid)));
+
+		/*
+		 * Build the set of equivalent CHECK constraints that this command
+		 * will attempt to change before visiting descendants. Each descendant
+		 * is compared to the original target constraint, not only by name.
+		 * The root itself has already been checked above.
+		 */
+		*changing_conids = lappend_oid(*changing_conids, currcon->oid);
+	}
+
+	/*
+	 * Update to the merged enforceability if needed. This may differ from the
+	 * requested enforceability when another matching parent constraint
+	 * remains enforced.
+	 */
 	if (currcon->conenforced != cmdcon->is_enforced)
 	{
 		AlterConstrUpdateConstraintEntry(cmdcon, conrel, contuple);
@@ -12702,7 +12775,8 @@ ATExecAlterCheckConstrEnforceability(List **wqueue, ATAlterConstraint *cmdcon,
 	/*
 	 * Note that we must recurse even when trying to change a check constraint
 	 * to not enforced if it is already not enforced, in case descendant
-	 * constraints might be enforced and need to be changed to not enforced.
+	 * constraints might be enforced and need to be changed to not enforced,
+	 * unless they still inherit an enforced constraint from another parent.
 	 * Conversely, we should do nothing if a constraint is being set to
 	 * enforced and is already enforced, as descendant constraints cannot be
 	 * different in that case.
@@ -12737,6 +12811,7 @@ ATExecAlterCheckConstrEnforceability(List **wqueue, ATAlterConstraint *cmdcon,
 
 			AlterCheckConstrEnforceabilityRecurse(wqueue, cmdcon, conrel,
 												  childoid, false, true,
+												  changing_conids,
 												  lockmode);
 		}
 	}
@@ -12788,6 +12863,7 @@ static void
 AlterCheckConstrEnforceabilityRecurse(List **wqueue, ATAlterConstraint *cmdcon,
 									  Relation conrel, Oid conrelid,
 									  bool recurse, bool recursing,
+									  List **changing_conids,
 									  LOCKMODE lockmode)
 {
 	SysScanDesc pscan;
@@ -12816,12 +12892,113 @@ AlterCheckConstrEnforceabilityRecurse(List **wqueue, ATAlterConstraint *cmdcon,
 				errmsg("constraint \"%s\" of relation \"%s\" does not exist",
 					   cmdcon->conname, get_rel_name(conrelid)));
 
+	if (!cmdcon->is_enforced)
+	{
+		Form_pg_constraint currcon;
+
+		currcon = (Form_pg_constraint) GETSTRUCT(childtup);
+
+		if (currcon->conenforced)
+			*changing_conids = list_append_unique_oid(*changing_conids,
+													  currcon->oid);
+	}
+
 	ATExecAlterCheckConstrEnforceability(wqueue, cmdcon, conrel, childtup,
-										 recurse, recursing, lockmode);
+										 recurse, recursing, changing_conids,
+										 lockmode);
 
 	systable_endscan(pscan);
 }
 
+/*
+ * When setting an inherited CHECK constraint to NOT ENFORCED, look for a
+ * matching parent constraint that remains ENFORCED and is not part of the same
+ * ALTER.
+ */
+static bool
+ATCheckCheckConstrHasEnforcedParent(Relation conrel, Relation rel,
+									HeapTuple contuple,
+									List **changing_conids,
+									Oid *enforced_parentoid)
+{
+	Form_pg_constraint currcon;
+	Relation	inhrel;
+	SysScanDesc scan;
+	ScanKeyData skey;
+	HeapTuple	inheritsTuple;
+
+	currcon = (Form_pg_constraint) GETSTRUCT(contuple);
+	Assert(currcon->contype == CONSTRAINT_CHECK);
+
+	if (currcon->coninhcount <= 0)
+		return false;
+
+	inhrel = table_open(InheritsRelationId, AccessShareLock);
+
+	ScanKeyInit(&skey,
+				Anum_pg_inherits_inhrelid,
+				BTEqualStrategyNumber, F_OIDEQ,
+				ObjectIdGetDatum(RelationGetRelid(rel)));
+	scan = systable_beginscan(inhrel, InheritsRelidSeqnoIndexId,
+							  true, NULL, 1, &skey);
+
+	while (HeapTupleIsValid(inheritsTuple = systable_getnext(scan)))
+	{
+		Oid			parentoid;
+		SysScanDesc pscan;
+		ScanKeyData pkey[3];
+		HeapTuple	parenttup;
+
+		parentoid = ((Form_pg_inherits) GETSTRUCT(inheritsTuple))->inhparent;
+
+		ScanKeyInit(&pkey[0],
+					Anum_pg_constraint_conrelid,
+					BTEqualStrategyNumber, F_OIDEQ,
+					ObjectIdGetDatum(parentoid));
+		ScanKeyInit(&pkey[1],
+					Anum_pg_constraint_contypid,
+					BTEqualStrategyNumber, F_OIDEQ,
+					ObjectIdGetDatum(InvalidOid));
+		ScanKeyInit(&pkey[2],
+					Anum_pg_constraint_conname,
+					BTEqualStrategyNumber, F_NAMEEQ,
+					NameGetDatum(&currcon->conname));
+
+		pscan = systable_beginscan(conrel, ConstraintRelidTypidNameIndexId,
+								   true, NULL, 3, pkey);
+
+		while (HeapTupleIsValid(parenttup = systable_getnext(pscan)))
+		{
+			Form_pg_constraint parentcon;
+
+			parentcon = (Form_pg_constraint) GETSTRUCT(parenttup);
+
+			if (list_member_oid(*changing_conids, parentcon->oid) ||
+				parentcon->contype != CONSTRAINT_CHECK ||
+				parentcon->connoinherit ||
+				!parentcon->conenforced)
+				continue;
+
+			if (constraints_equivalent(parenttup, contuple,
+									   RelationGetDescr(conrel)))
+			{
+				*enforced_parentoid = parentoid;
+				systable_endscan(pscan);
+				systable_endscan(scan);
+				table_close(inhrel, AccessShareLock);
+				return true;
+			}
+		}
+
+		systable_endscan(pscan);
+	}
+
+	systable_endscan(scan);
+	table_close(inhrel, AccessShareLock);
+
+	return false;
+}
+
 /*
  * Returns true if the constraint's deferrability is altered.
  *
diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out
index e54fec7fb57..b649d10e36b 100644
--- a/src/test/regress/expected/constraints.out
+++ b/src/test/regress/expected/constraints.out
@@ -446,8 +446,12 @@ 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;
-alter table parted_ch_2 alter constraint cc not enforced;
-alter table parted_ch_2 alter constraint cc_1 not enforced;
+alter table parted_ch_2 alter constraint cc not enforced; -- error
+ERROR:  cannot mark inherited constraint "cc" as NOT ENFORCED because matching constraint on parent table "parted_ch" is ENFORCED
+alter table only parted_ch_2 alter constraint cc not enforced; -- error
+ERROR:  cannot mark inherited constraint "cc" as NOT ENFORCED because matching constraint on parent table "parted_ch" is ENFORCED
+alter table parted_ch_2 alter constraint cc_1 not enforced; -- error
+ERROR:  cannot mark inherited constraint "cc_1" as NOT ENFORCED because matching constraint on parent table "parted_ch" is ENFORCED
 alter table parted_ch_2 alter constraint cc_2 not enforced;
 --check these CHECK constraint status again
 select * from check_constraint_status;
@@ -457,12 +461,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..5b87083a870 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -1479,6 +1479,62 @@ NOTICE:  drop cascades to 3 other objects
 DETAIL:  drop cascades to table p1_c1
 drop cascades to table p1_c2
 drop cascades to table p1_c3
+-- an inherited CHECK constraint cannot be NOT ENFORCED under an ENFORCED parent
+create table p1(f1 int constraint p1_a_check check (f1 > 0) enforced);
+create table p1_c1() inherits(p1);
+alter table p1_c1 alter constraint p1_a_check not enforced; -- error
+ERROR:  cannot mark inherited constraint "p1_a_check" as NOT ENFORCED because matching constraint on parent table "p1" is ENFORCED
+alter table p1 alter constraint p1_a_check not enforced; -- ok
+alter table p1_c1 alter constraint p1_a_check not enforced; -- ok
+drop table p1 cascade;
+NOTICE:  drop cascades to table p1_c1
+-- recursive NOT ENFORCED merges with ENFORCED constraints from other parents
+create table p1(a int constraint p1_a_check check (a > 0) enforced);
+create table p2(a int constraint p1_a_check check (a > 0) enforced);
+create table p1_c1() inherits (p1, p2);
+NOTICE:  merging multiple inherited definitions of column "a"
+alter table p1 alter constraint p1_a_check not enforced; -- ok
+select  conname, conenforced, convalidated, conrelid::regclass
+from    pg_constraint
+where   conname = 'p1_a_check' and contype = 'c'
+order by conrelid::regclass::text collate "C";
+  conname   | conenforced | convalidated | conrelid 
+------------+-------------+--------------+----------
+ p1_a_check | f           | f            | p1
+ p1_a_check | t           | t            | p1_c1
+ p1_a_check | t           | t            | p2
+(3 rows)
+
+alter table p1_c1 alter constraint p1_a_check not enforced; -- error
+ERROR:  cannot mark inherited constraint "p1_a_check" as NOT ENFORCED because matching constraint on parent table "p2" is ENFORCED
+alter table p2 alter constraint p1_a_check not enforced; -- ok
+alter table p1_c1 alter constraint p1_a_check not enforced; -- ok. Since two parents are not enforced too.
+drop table p1, p2 cascade;
+NOTICE:  drop cascades to table p1_c1
+-- recursive NOT ENFORCED can change all matching enforced parents together
+create table gp(a int constraint gp_a_check check (a > 0) enforced);
+create table p1() inherits (gp);
+create table p2() inherits (gp);
+create table p1_c1() inherits (p1, p2);
+NOTICE:  merging multiple inherited definitions of column "a"
+alter table gp alter constraint gp_a_check not enforced; -- ok
+select  conname, conenforced, convalidated, conrelid::regclass
+from    pg_constraint
+where   conname = 'gp_a_check' and contype = 'c'
+order by conrelid::regclass::text collate "C";
+  conname   | conenforced | convalidated | conrelid 
+------------+-------------+--------------+----------
+ gp_a_check | f           | f            | gp
+ gp_a_check | f           | f            | p1
+ gp_a_check | f           | f            | p1_c1
+ gp_a_check | f           | f            | p2
+(4 rows)
+
+drop table gp cascade;
+NOTICE:  drop cascades to 3 other objects
+DETAIL:  drop cascades to table p1
+drop cascades to table p2
+drop cascades to table p1_c1
 --for "no inherit" check constraint, it will not recurse to child table
 create table p1(f1 int constraint p1_a_check check (f1 > 0) no inherit not enforced);
 create table p1_c1(f1 int constraint p1_a_check check (f1 > 0) not enforced);
diff --git a/src/test/regress/sql/constraints.sql b/src/test/regress/sql/constraints.sql
index dc133b124bb..26a9ac78c96 100644
--- a/src/test/regress/sql/constraints.sql
+++ b/src/test/regress/sql/constraints.sql
@@ -309,8 +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;
-alter table parted_ch_2 alter constraint cc not enforced;
-alter table parted_ch_2 alter constraint cc_1 not enforced;
+alter table parted_ch_2 alter constraint cc not enforced; -- error
+alter table only parted_ch_2 alter constraint cc not enforced; -- error
+alter table parted_ch_2 alter constraint cc_1 not enforced; -- error
 alter table parted_ch_2 alter constraint cc_2 not enforced;
 
 --check these CHECK constraint status again
diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql
index 8f986904389..679b0b2d969 100644
--- a/src/test/regress/sql/inherit.sql
+++ b/src/test/regress/sql/inherit.sql
@@ -535,6 +535,40 @@ where   conname = 'inh_check_constraint3' and contype = 'c'
 order by conrelid::regclass::text collate "C";
 drop table p1 cascade;
 
+-- an inherited CHECK constraint cannot be NOT ENFORCED under an ENFORCED parent
+create table p1(f1 int constraint p1_a_check check (f1 > 0) enforced);
+create table p1_c1() inherits(p1);
+alter table p1_c1 alter constraint p1_a_check not enforced; -- error
+alter table p1 alter constraint p1_a_check not enforced; -- ok
+alter table p1_c1 alter constraint p1_a_check not enforced; -- ok
+drop table p1 cascade;
+
+-- recursive NOT ENFORCED merges with ENFORCED constraints from other parents
+create table p1(a int constraint p1_a_check check (a > 0) enforced);
+create table p2(a int constraint p1_a_check check (a > 0) enforced);
+create table p1_c1() inherits (p1, p2);
+alter table p1 alter constraint p1_a_check not enforced; -- ok
+select  conname, conenforced, convalidated, conrelid::regclass
+from    pg_constraint
+where   conname = 'p1_a_check' and contype = 'c'
+order by conrelid::regclass::text collate "C";
+alter table p1_c1 alter constraint p1_a_check not enforced; -- error
+alter table p2 alter constraint p1_a_check not enforced; -- ok
+alter table p1_c1 alter constraint p1_a_check not enforced; -- ok. Since two parents are not enforced too.
+drop table p1, p2 cascade;
+
+-- recursive NOT ENFORCED can change all matching enforced parents together
+create table gp(a int constraint gp_a_check check (a > 0) enforced);
+create table p1() inherits (gp);
+create table p2() inherits (gp);
+create table p1_c1() inherits (p1, p2);
+alter table gp alter constraint gp_a_check not enforced; -- ok
+select  conname, conenforced, convalidated, conrelid::regclass
+from    pg_constraint
+where   conname = 'gp_a_check' and contype = 'c'
+order by conrelid::regclass::text collate "C";
+drop table gp cascade;
+
 --for "no inherit" check constraint, it will not recurse to child table
 create table p1(f1 int constraint p1_a_check check (f1 > 0) no inherit not enforced);
 create table p1_c1(f1 int constraint p1_a_check check (f1 > 0) not enforced);
-- 
2.34.1

---

From: Chao Li <[email-masked]>
Date: Tue, 2 Jun 2026 14:24:01 +0800

> On Jun 1, 2026, at 23:51, jian he <[email-masked]> wrote:
> 
> Hi.
> 

I agree that ATGetEquivalentCheckConstraintOid is not needed. I was too worried that looking up descendant constraints by name alone was not good enough. But now I realize that users have no supported way to define a conflicting constraint on a child with the same name as a parent’s inheritable CHECK constraint. In v5, I removed ATGetEquivalentCheckConstraintOid and switched to get_relation_constraint_oid.

> aligning with existing code conventions.

The v3 code was a little confusing. In fact, changing_conids only needs to be built at the top level, so list_copy() is not needed, and List ** is not needed either. I refactored this part in v5.

> <v4-0001-Prevent-inherited-CHECK-constraints-from-being-weakened.patch>

v4 is similar to my original implementation when I worked on v2, and it does not handle some complicated inheritance cases correctly. The following 2 tests are based on v4.

1
```
evantest=# create table root(a int constraint ck check (a > 0) enforced);
CREATE TABLE
evantest=# create table ext(a int constraint ck check (a > 0) enforced);
CREATE TABLE
evantest=# create table mixed() inherits (root, ext);
NOTICE:  merging multiple inherited definitions of column "a"
CREATE TABLE
evantest=# create table ordinary() inherits (root);
CREATE TABLE
evantest=# alter table root alter constraint ck not enforced;
ALTER TABLE
evantest=# select conrelid::regclass, conname, conenforced, coninhcount, conislocal from pg_constraint where conname='ck';
 conrelid | conname | conenforced | coninhcount | conislocal
----------+---------+-------------+-------------+------------
 root     | ck      | f           |           0 | t
 ext      | ck      | t           |           0 | t
 mixed    | ck      | t           |           2 | f
 ordinary | ck      | t           |           1 | f
(4 rows)
```

In this test, ordinary only inherits from root, so when root is changed to NOT ENFORCED, ordinary should be changed as well. However, ordinary incorrectly remains ENFORCED.

2
```
evantest=# create table root(a int constraint ck check (a > 0) enforced);
CREATE TABLE
evantest=# create table child() inherits (root);
CREATE TABLE
evantest=# create table intermediate() inherits (root);
CREATE TABLE
evantest=# alter table child inherit intermediate;
ALTER TABLE
evantest=# alter table root alter constraint ck not enforced;
ALTER TABLE
evantest=# select conrelid::regclass, conname, conenforced, coninhcount, conislocal from pg_constraint where conname='ck';
   conrelid   | conname | conenforced | coninhcount | conislocal
--------------+---------+-------------+-------------+------------
 root         | ck      | f           |           0 | t
 child        | ck      | t           |           2 | f
 intermediate | ck      | t           |           1 | f
(3 rows)
```

This is a more complicated case. The tricky part is that child is altered to inherit from intermediate, but child was created before intermediate, so child has a smaller OID than intermediate, which affects the recursion order.

Ultimately, both child and intermediate inherit from root, so after root is changed to NOT ENFORCED, they should be changed to NOT ENFORCED as well. But in this test, they incorrectly remain ENFORCED.

Both tests pass with v3 and v5:

PFA v5:

0001:
 * Removed ATGetEquivalentCheckConstraintOid
 * Refactored how changing_conids is built
 * Added two test cases that Jian added to v4
 * Added the complicated inheritance test cases shown above

0002: Same as v3-0002; fixes the documentation issue Alvaro pointed out earlier

0003: The documentation change from [1], which clarifies the ALTER TABLE doc about constraint enforceability

BTW, Jian, next time, would you mind attaching a diff on top of the previous patch version? That would make your changes easier to identify.

[1] https://www.postgresql.org/message-id/711B1ED3-1781-4B6C-A573-B58AF20770E5%40gmail.com

Best regards,

---

From: Chao Li <[email-masked]>
Date: Tue, 2 Jun 2026 14:39:21 +0800

> On Jun 2, 2026, at 14:24, Chao Li <[email-masked]> wrote:
> 
> 
> 
> <v5-0001-Prevent-inherited-CHECK-constraints-from-being-we.patch><v5-0002-doc-Clarify-inherited-constraint-behavior.patch><v5-0003-doc-Clarify-ALTER-CONSTRAINT-enforceability-behav.patch>

Oops! I just found that I forgot to commit a tiny comment tuning in 0001. So posting v6.

Best regards,

---

From: Zsolt Parragi <[email-masked]>
Date: Tue, 2 Jun 2026 15:56:40 -0500

Hello

This means once is_enforced is set to true, it will never be
rechecked. See the following example which showcases an issue with
this:

create table g(a int constraint k check(a > 0) enforced);
create table o(a int constraint k check(a > 0) enforced);
create table s1() inherits(g);
create table s2() inherits(g, o);
create table s3() inherits(g);
alter table g alter constraint k not enforced;
select conrelid::regclass as tbl, conenforced
from   pg_constraint
where  conname = 'k' and contype = 'c'
order  by conrelid::regclass::text collate "C";
insert into s1 values (-1);
insert into s3 values (-1);   -- bug: ERRORs out, but shouldn't

---

From: Chao Li <[email-masked]>
Date: Wed, 3 Jun 2026 09:54:01 +0800

> On Jun 3, 2026, at 04:56, Zsolt Parragi <[email-masked]> wrote:
> 
> Hello
> 

Hi Zsolt,

Thanks for your view.

I just tried your test with v6, but didn't see the bug you mentioned:
```
evantest=# create table g(a int constraint k check(a > 0) enforced);
CREATE TABLE
evantest=# create table o(a int constraint k check(a > 0) enforced);
CREATE TABLE
evantest=# create table s1() inherits(g);
CREATE TABLE
evantest=# create table s2() inherits(g, o);
NOTICE:  merging multiple inherited definitions of column "a"
CREATE TABLE
evantest=# create table s3() inherits(g);
CREATE TABLE
evantest=# alter table g alter constraint k not enforced;
ALTER TABLE
evantest=# select conrelid::regclass as tbl, conenforced
evantest-# from   pg_constraint
evantest-# where  conname = 'k';
 tbl | conenforced
-----+-------------
 o   | t
 s2  | t
 g   | f
 s1  | f
 s3  | f
(5 rows)

evantest=# insert into s1 values (-1);
INSERT 0 1
evantest=# insert into s3 values (-1);
INSERT 0 1
```

Here, both s1 and s3 are the direct child of g, thus when g is set to “not enforced”, s1 and s3 is set to “not enforced" as well. While s2 is a child of both g and o, as o is still enforced, s2 remains enforced. The test result is right as expected.

Best regards,

---

From: jian he <[email-masked]>
Date: Wed, 3 Jun 2026 14:58:45 +0800

Hi.

One partition cannot have two direct parent tables. see
ATExecAttachPartition comment:
/* A partition can only have one parent */

Also
CREATE TABLE ... PARTITION OF automatically copies the parent
constraint definition, and
ALTER TABLE ... ATTACH PARTITION already rejects cases where the
parent is enforced but the child is not.

Therefore, I think calling ATCheckCheckConstrHasEnforcedParent once is
enough for partition inheritance.

Also please check the attached minor refactoring.

---

From: Zsolt Parragi <[email-masked]>
Date: Wed, 3 Jun 2026 03:12:31 -0500

> I just tried your test with v6, but didn't see the bug you mentioned:

Eh, sorry, that was a user error on my part. There were 3 new patch
versions in a short timeframe, I applied one, built postgres, realized
it's not the latest, applied the latest instead... and didn't rebuild
postgres. It's not reproducible with your latest patch.

---

From: Chao Li <[email-masked]>
Date: Wed, 3 Jun 2026 16:17:24 +0800

> On Jun 3, 2026, at 14:58, jian he <[email-masked]> wrote:
> 

This is a good point. I addressed it in v6.

> Also please check the attached minor refactoring.

I accepted the refactoring with a small tuning. When we pull up changing_conids = list_make1_oid(currcon->oid); to before the for loop, we also need to check if (!cmdcon->is_enforced). I also updated the comment accordingly.

> <checkconstr_inheritance_issue.nocfbot>

Also, thanks for attaching the diff, which made me easy to view your refactoring by applying the diff to my patch branch.

PFA v6: 0002 and 0003 unchanged. 0001 addressed the two comments from Jian.

Best regards,

---

From: Zsolt Parragi <[email-masked]>
Date: Wed, 3 Jun 2026 07:36:53 -0700

Hello

After a bit more testing, I think there's still a remaining issue with
the latest patch:

create table root_t (a int constraint c check (a > 0) enforced);
create table p2     (a int constraint c check (a > 0) enforced);
create table d () inherits (root_t, p2);
create table e () inherits (d);
create table f () inherits (e);
alter table root_t alter constraint c not enforced;
insert into e values (-5); -- succeeds

d remains enforced as it should, but e and f doesn't.

---

From: Chao Li <[email-masked]>
Date: Thu, 4 Jun 2026 06:17:09 +0800

> On Jun 3, 2026, at 22:36, Zsolt Parragi <[email-masked]> wrote:
> 
> Hello
> 

Thanks for your testing. Yeah, inheritance cases are really complicated. find_all_inheritors() returns a list of the root plus its descendants, but it cannot ensure that a parent always appears before its child. For example:
```
create table gp(a int constraint c check (a > 0) enforced);
create table d() inherits (gp);
create table p1() inherits (gp);
alter table d inherit p1;
```

In this case, d is a child of both gp and p1. But because d is created before p1, d has a smaller OID than p1. So even though p1 is a parent of d, d can still appear before p1 in the list, like gp -> d -> p1.

That’s why I built the changing_conids list in the implementation. But I wrongly assumed that all constraints in the list would be updated to NOT ENFORCED. Your test case uncovered that this assumption is wrong. So when a parent appears in changing_conids, we have to recurse upward to see if there is a parent outside the current ALTER TABLE that may affect the result. The fix is in ATCheckCheckConstrHasEnforcedParent().

See attached v7 for details. With v7, your test case passes. I have added this test case to the regression tests, and made it even more complicated.
```
evantest=# create table root_t (a int constraint c check (a > 0) enforced);
CREATE TABLE
evantest=# create table p2     (a int constraint c check (a > 0) enforced);
CREATE TABLE
evantest=# create table d () inherits (root_t, p2);
NOTICE:  merging multiple inherited definitions of column "a"
CREATE TABLE
evantest=# create table e () inherits (d);
CREATE TABLE
evantest=# create table f () inherits (e);
CREATE TABLE
evantest=# alter table root_t alter constraint c not enforced;
ALTER TABLE
evantest=#
evantest=# select conrelid::regclass as tbl, conenforced from pg_constraint where  conname = 'c';
  tbl   | conenforced
--------+-------------
 d      | YES
 e      | YES
 f      | YES
 root_t | NO
 p2     | YES
(5 rows)
```
Now, both e and f remain ENFORCE.

Best regards,

---

From: Zsolt Parragi <[email-masked]>
Date: Thu, 4 Jun 2026 12:08:02 -0700

The new version looks correct, I don't see any logic problem with it,
however, I do have a performance question:

+				/*
+				 * A parent listed in changing_conids is being changed by the
+				 * same ALTER, but it may not have been updated yet.  For
+				 * regular inheritance, recurse upward to check whether an
+				 * equivalent enforced parent outside the ALTER will make it
+				 * remain enforced.  Partitions cannot have multiple parents,
+				 * so they do not need this check.
+				 */
+				if (!rel->rd_rel->relispartition &&
+					list_member_oid(changing_conids, parentcon->oid))

Shouldn't the parent lookup use some form of caching? Otherwise we'll
end up reevaluating the same parents multiple times. I'm not sure if
it is needed or not, how much of a performance impact this can have in
a real-world server.

---

From: Chao Li <[email-masked]>
Date: Fri, 5 Jun 2026 08:35:56 +0800

> On Jun 5, 2026, at 03:08, Zsolt Parragi <[email-masked]> wrote:
> 

Actually, after sending v7, I spent several hours trying to cache update history, which could avoid some recurse-up lookups. But I was uncomfortable with the resulting code and gave up on that approach. There were several reasons:

* Re-evaluation only happens in an edge case where all of the following are true:
   - the constraint is altered from ENFORCED to NOT ENFORCED
   - the target is a regular inherited table, partitioned tables do not go through this logic
   - the inheritance graph is complex, as in your test case, only tables like e and f hit the re-evaluation case
   
* It is common for a partitioned table to have thousands of partitions, but I rarely hear of a regular inherited table having thousands of descendants.

* This re-evaluation only reads catalog tables. Compared with ALTER TABLE operations that rewrite table data, the cost should not be too bad.

* More importantly, I don’t think caching update results is the right solution. The root cause is that find_all_inheritors() cannot ensure that a child appears after all of its parents in the returned list. We could add an alternative helper that returns inheritance descendants in topological order. With that, the current recurse-up logic could be avoided, and maybe the changing_oids list could be avoided as well. But it is too late for v19. I have added this to my TODO list and will work on it for v20.

In summary, I think the current re-evaluation has very limited performance impact and is acceptable for v19. We can improve the algorithm with a better solution in v20.

BTW, do you have any comments on the doc changes in 0002 and 0003?

Best regards,

---

From: Zsolt Parragi <[email-masked]>
Date: Fri, 5 Jun 2026 11:44:51 -0700

> * It is common for a partitioned table to have thousands of partitions, but I rarely hear of a regular inherited table having thousands of descendants.

Yes, that's why I also wasn't sure if it's needed or not, I can
construct scenarios where alters take a relatively long time, but
those do not seem to be realistic at all... so maybe it's an
acceptable limitation as you said.

> BTW, do you have any comments on the doc changes in 0002 and 0003?

+   (check and not-null constraints) down the inheritance hierarchy.  With
+   multiple inheritance, if a column or constraint is inherited from more
+   than one parent, the stricter definition applies.

The documentation changes generally look good to me, but should this
statement be part of the alter table description? There's a paragraph
about merge rules above which might be a better fit for it.
("Inheritable check constraints and not-null constraints are merged in
a similar fashion")

---

From: Chao Li <[email-masked]>
Date: Sat, 6 Jun 2026 11:06:00 +0800

> On Jun 6, 2026, at 02:44, Zsolt Parragi <[email-masked]> wrote:
> 

Make sense.

PFA v8: 0001 and 0003 unchanged. 0002  addressed Zsolt’s comment.

Best regards,

---

From: jian he <[email-masked]>
Date: Mon, 8 Jun 2026 18:28:55 +0800

> PFA v8: 0001 and 0003 unchanged. 0002  addressed Zsolt’s comment.

drop table root_t cascade;
create table root_t (a int constraint c check (a > 0) enforced);
create table p2 (a int constraint c check (a > 0) enforced);
create table d () inherits (root_t, p2);
create table e () inherits (d);
CREATE OR REPLACE PROCEDURE create_table(i int) AS $$
DECLARE
  r int;
  query text;
BEGIN
    FOR i IN 20..$1 LOOP
        query := 'create table f' || i || '() inherits (e)';
        raise notice 'query: %', query;
        EXECUTE query;
    END LOOP;
END; $$ LANGUAGE plpgsql;

call create_table(1000);

ATCheckCheckConstrHasEnforcedParent invoked 996070 times
ATCheckCheckConstrHasEnforcedParent will call itself, so I think we
need check_stack_depth() on it.

I also made a minor refactoring to reduce unnecessary work in
ATCheckCheckConstrHasEnforcedParent().
A single call to table_open(parentoid, ...) is enough.

Keep errmsg() messages on a single line to improve grepability.

---

From: Chao Li <[email-masked]>
Date: Mon, 8 Jun 2026 19:51:33 +0800

> On Jun 8, 2026, at 18:28, jian he <[email-masked]> wrote:
> 

Thanks for the review.

> need check_stack_depth() on it.

Good point. Added it in v9.

> A single call to table_open(parentoid, ...) is enough.

Agreed. I fine-tuned this code a little bit further. Now in v9, it only opens the parent table when needed and only open at most once.

> Keep errmsg() messages on a single line to improve grepability.

I’m not sure this needs to be changed. The message is quite long, and there are existing precedents for splitting long errmsg() strings across multiple lines. For example:
```
	if (getExtensionOfObject(NamespaceRelationId, nspOid) == extensionOid)
		ereport(ERROR,
				(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
				 errmsg("cannot move extension \"%s\" into schema \"%s\" "
						"because the extension contains the schema",
						extensionName, newschema)));
```

So, this does not seem to be a strict rule. I also don’t think this hurts grepability much in practice, since searching for the distinctive part of the message still finds it.

Best regards,

---

From: Álvaro Herrera <[email-masked]>
Date: Mon, 8 Jun 2026 16:52:49 +0200

> > Keep errmsg() messages on a single line to improve grepability.
G 
> I’m not sure this needs to be changed. The message is quite long, and
> there are existing precedents for splitting long errmsg() strings
> across multiple lines. For example:
> ```
> So, this does not seem to be a strict rule. I also don’t think this
> hurts grepability much in practice, since searching for the
> distinctive part of the message still finds it.

Personally I find myself upset whenever I come across messages split in
this way.  Kindly do not add more if you can avoid it.  It [my dislike
for that] is not strong enough to create a commit to stitch them back,
but I frequently do so in my local editor and later discard the change
when collecting changes to commit via "git add -p".

Greppability is subjective -- you don't know which part is
"distinctive".  For instance, when looking for message
refactoring/rewording I grep for things such as "cannot.*schema" or
such, and any arbitrary splitting could potentially thwart that.

Lastly, when the message is in a single line, grammatical mistakes are
easier to see, such as the missing "the" in 
   errmsg("cannot mark inherited constraint \"%s\" as NOT ENFORCED because [the] matching constraint on parent table \"%s\" is ENFORCED", ...)

In this case I would also move the NOT ENFORCED clause out of the
translatable message and replace it with %s; and I'm wondering whether
the part after "because" should be errdetail or not.

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/
"Having your biases confirmed independently is how scientific progress is
made, and hence made our great society what it is today" (Mary Gardiner)

---

From: Chao Li <[email-masked]>
Date: Tue, 9 Jun 2026 08:32:19 +0800

> On Jun 8, 2026, at 22:52, Álvaro Herrera <[email-masked]> wrote:
> 

Thanks for the explanation. I will treat that as a rule in future when working for PG code. So, noted.

> the part after "because" should be errdetail or not.

In v10, I split the “because” part to a errdetail, also moved out NOT ENFORCED out of the translate message.

Best regards,

---

From: jian he <[email-masked]>
Date: Wed, 17 Jun 2026 11:27:31 +0800

> In v10, I split the “because” part to a errdetail, also moved out NOT ENFORCED out of the translate message.

ATCheckCheckConstrHasEnforcedParent
``````
            if (constraints_equivalent(parenttup, contuple,
                                       RelationGetDescr(conrel)))
            {
``````
The above IF condition is basically always true (see
MergeConstraintsIntoExisting), unless an inherited check constraint
has a
different definition, which should not happen.
So I did a quick refactor here, which also drops the nesting level down by one.

Other than that, v10 looks good to me.

---

From: Chao Li <[email-masked]>
Date: Wed, 17 Jun 2026 15:22:44 +0800

> On Jun 17, 2026, at 11:27, jian he <[email-masked]> wrote:
> 
> <v10-0001-misc-refactor-ATCheckCheckConstrHasEnforcedParent.nocfbot>

Thanks for your suggestion, I think that is correct.

PFA v11 - 0001 integrated Jian’s change; 0002 and 0003 unchanged.

Best regards,