> On May 26, 2026, at 11:51, Chao Li <[email protected]> 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/[email protected] Best regards, -- Chao Li (Evan) HighGo Software Co., Ltd. https://www.highgo.com/
v2-0001-Fix-CHECK-constraint-enforceability-recursion.patch
Description: Binary data
v2-0002-doc-Clarify-ALTER-CONSTRAINT-enforceability-behav.patch
Description: Binary data
