> On Oct 9, 2026, at 12:42, shihao zhong <[email protected]> wrote:
>
> Hi hackers,
>
> I used Opus to analyze the new features in PG19, and it found three
> bugs with a CHECK constraint that a child enforces and its parent
> does not. Each item has a script and a patch with its number.
> "Before" is 19 and master today. "After" is with the patch, and is
> the output I expect.
>
> 1. ALTER CONSTRAINT ... ENFORCED on the parent does not check a child
> whose constraint is ENFORCED but NOT VALID. The parent is marked
> validated anyway, so a query on it can miss the child's bad row.
>
> create table p (a int, constraint c check (a > 0) not enforced);
> create table k (a int);
> insert into k values (-1);
> alter table k add constraint c check (a > 0) not valid;
> alter table k inherit p;
> alter table p alter constraint c enforced;
> select count(*) from (select a from p union all select 1) s
> where a < 0;
> Before:
> ALTER TABLE
> count
> -------
> 0
> After 0001:
> ERROR: check constraint "c" of relation "k" is violated by some row
> count
> -------
> 1
>
I can reproduce the bug, and the fix in 0001 looks good.
However, this bug report raised a concern that I didn’t notice while working on
my previous enforceability fix [1].
This bug report relies on ALTER TABLE p ALTER CONSTRAINT c ENFORCED also
changing the child table k’s constraint from NOT VALID to VALID.
I verified the following on PG18:
```
evantest=# create table t(a int);
CREATE TABLE
evantest=# insert into t values (-1);
INSERT 0 1
evantest=# alter table t add constraint c check (a>0) enforced not valid;
ALTER TABLE
evantest=#
evantest=# select conenforced, convalidated from pg_constraint where
conname='c';
conenforced | convalidated
-------------+--------------
t | f
(1 row)
```
The test shows that a CHECK constraint can be both ENFORCED and NOT VALID. This
tests adding a constraint, however, rather than changing its enforceability.
Returning to your reproducer, after altering k to inherit from p, k’s
constraint c remains NOT VALID:
```
evantest=# create table p (a int, constraint c check (a > 0) not enforced);
CREATE TABLE
evantest=# create table k (a int);
CREATE TABLE
evantest=# insert into k values (-1);
INSERT 0 1
evantest=# alter table k add constraint c check (a > 0) not valid;
ALTER TABLE
evantest=# alter table k inherit p;
ALTER TABLE
evantest=# select conrelid::regclass, conenforced, convalidated from
pg_constraint where conname='c';
conrelid | conenforced | convalidated
----------+-------------+--------------
p | f | f
k | t | f
(2 rows)
```
So the design question here is whether changing a constraint from NOT ENFORCED
to ENFORCED should automatically validate it.
I read through the original discussion at [2]. It explicitly mentions
validating existing rows, but I didn’t find a discussion of whether NOT VALID
should be retained. That might be reasonable, because the original patch
followed the existing behavior for changing foreign key constraints to ENFORCED.
IMO, changing enforceability should retain NOT VALID. However, I understand
that the current implementation validates existing rows, so this patch fixes a
bug under those semantics. My concern is with the original design, and it may
be too late to reopen that discussion at this point. I will create a separate
thread for v20.
[1]
https://www.postgresql.org/message-id/E74C57FA-1DD0-4C8E-8FB1-538034752592%40gmail.com
[2]
https://postgr.es/m/cacjufxhch_fu-fsewscvg9mn6-5tzr6h9ntn+0kugtcaerd...@mail.gmail.com
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
