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

On Thu, Oct 15, 2026 at 11:48 AM shihao zhong <zhong950419(at)gmail(dot)com> wrote:
>
> 3. Dumping the child's constraint as local changes the catalog. With
> your patch, k's c comes back with conislocal = true, so ALTER TABLE p
> DROP CONSTRAINT c no longer drops it from k. pg_upgrade keeps it
> inherited today, and your patch makes it local there too. ALTER
> CONSTRAINT is the only command that gives back an inherited
> constraint that only the child enforces.
>

https://www.postgresql.org/message-id/202410011520.zl4go2gzgzwn%40alvherre.pgsql
mentioned in some case, it's ok for pg_dump changing conislocal status.
Maybe it does not apply to this case.
However, my guess is that for the SQL below, some constraint is bound
to change conislocal.
because adding not valid constraint happens very later of pg_dump.

drop table if exists p3 cascade;
create table p3 (a int);
alter table p3 add constraint c check (a > 0) not valid;
create table k3 () inherits (p3);

> Your partition case is real, my 0003 missed it, and pg_upgrade loses
> it on master too. v2-0003 dumps that constraint before its ancestors'
> ones, as your patch does, with your test. I added you as co-author.
>
> A local NOT VALID child that sorts after the parent still comes back
> validated. That comes from ADD CONSTRAINT merging into the inherited
> NOT ENFORCED copy, which Nikolay's patch fixes [1].
>
> [1] https://postgr.es/m/CAM527d8qCH4q1xV-TftDmm3eBcBie9AxfdTtThPUoGWnYT08gw@mail.gmail.com
>

There are 2 more bugs related after playing around with your v2-0003.
Bug A affects PG18 and earlier (reproduced on PG16).
The following scirpt pg_dump output is wrong.

drop table if exists p3 cascade;
create table p3 (a int);
alter table p3 add constraint c check (a > 0) not valid;
create table k3 () inherits (p3);
alter table k3 validate constraint c;
----------------------------------------------------
Bug B affects PG18.
Restoring the pg_dump output of the script below into an existing cluster fails.

drop table if exists tp, tc cascade;
create table tp (a int);
create table tc (a int) inherits (tp);
alter table tc add constraint nn not null a not valid;
alter table tp alter column a set not null;

BUGB root cause is
https://git.postgresql.org/cgit/postgresql.git/commit/?id=96e2af605043974137d84edf5c0a24561956919e

I am using
pg_dump --table-and-children=pattern --no-owner --no-acl --clean --no-data
for testing.

Since these bugs are related, posting them in this thread seems appropriate.

----------------------------------------------------------------------------------------
> 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.

This issue dates back to at least PG14.
See https://dbfiddle.uk/AO112Bxp.
I think we need a unified way to restore the old constraint's properties.
The attached WIP patch only makes ALTER COLUMN SET DATA TYPE and ALTER COLUMN
SET EXPRESSION preserve pg_constraint.conenforced and convalidated of a
child's constraint. It does not try to preserve any other constraint
properties.

Since this is an old bug, I tend to think it's not an open item for PG19.

--
jian
https://www.enterprisedb.com/