Re: Fix bug of CHECK constraint enforceability recursion

> On May 26, 2026, at 11:51, Chao Li <li(dot)evan(dot)chao(at)gmail(dot)com> wrote:
>
> 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/
>
>
>
>
> <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/711B1ED3-1781-4B6C-A573-B58AF20770E5@gmail.com

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