On Fri, Oct 9, 2026 at 12:42 PM 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 >
Here I think we need to validate table k; otherwise the parent ends up valid while the child is not valid. However, ALTER CONSTRAINT ENFORCED does not validate a constraint that is already ENFORCED NOT VALID. So the behavior becomes inconsistent: ALTER CONSTRAINT ENFORCED would validate ENFORCED NOT VALID constraint when it reaches it through the parent, but not when it is run on that constraint directly. The following demo yields no error in PG18. ---------------------- begin; drop table if exists pktable, fktable; CREATE TABLE pktable ( ptest1 int PRIMARY KEY, ptest2 text ); CREATE TABLE fktable ( ftest1 int, ftest2 int); insert into fktable values (1, 2); alter table fktable add CONSTRAINT fk1 foreign key (ftest1) REFERENCES PKTABLE(ptest1) NOT valid; alter table fktable alter constraint fk1 enforced; rollback; ---------------------- > 2. ALTER COLUMN TYPE on the parent turns the child's constraint back > to NOT ENFORCED. The child's constraint is dropped and recreated from > the parent's definition. > > create table p2 (a int, constraint c check (a > 0) not enforced); > create table k2 () inherits (p2); > alter table k2 alter constraint c enforced; > alter table p2 alter column a type bigint; > insert into k2 values (-1); > Before: > INSERT 0 1 > After 0002: > ERROR: new row for relation "k2" violates check constraint "c" > DETAIL: Failing row contains (-1). > This is a pre-existing bug/behavior: when a child table's constraint is in a different state from the parent's (for example, ENFORCED on the child but NOT ENFORCED on the parent), ALTER COLUMN ... SET DATA TYPE and ALTER COLUMN ... SET EXPRESSION recreate the child's constraint from the parent's definition, so the child silently ends up with the parent's state. See the demo below in pg18. begin; drop table if exists pp, pp1; create table pp (k int, f1 int generated always as (1) stored) partition by list (k); alter table pp add constraint c check (f1 > 0) not enforced; create table pp1 (k int, f1 int generated always as (1) stored constraint c check (f1 > 0)); alter table pp attach partition pp1 for values in (1); alter table pp alter column f1 type bigint; \d+ pp1 rollback; -------------------------------- -- jian https://www.enterprisedb.com/
