Hi,
I just tested “Add support for altering CHECK constraint enforceability” and found an issue where recursion is not
handledproperly.
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
NOTENFORCED. 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
bothpartitioned tables and inheritance children can currently be altered to have different enforceability. So we cannot
relyon 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/
