Re: Fix bug of CHECK constraint enforceability recursion

> On Jun 3, 2026, at 22:36, Zsolt Parragi <zsolt(dot)parragi(at)percona(dot)com> wrote:
>
> 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.
>
>

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,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/