Re: [PG19] Three bugs with a CHECK constraint that only the child enforces

On Fri, Oct 9, 2026 at 12:42 PM shihao zhong <zhong950419(at)gmail(dot)com> 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/