On Tue, Oct 20, 2026 at 10:24 AM shihao zhong <[email protected]> wrote:
>
> Hi jian,
>
> v4 looks good to me. One case is still lost after dump and restore,
> on master and 19, and also on 18:
>
> create table p1 (a int, constraint c check (a > 0));
> create table p2 (a int, constraint c check (a > 0) not enforced);
> create table k () inherits (p1, p2);
> alter table k no inherit p1;
>
> k's c is not local here, so it comes back NOT ENFORCED.
Great!
mimic the above for convalidated, we can easily find out a very old
pg_dump bug. (at least PG16)
drop table if exists p1, p2, k cascade;
create table p1 (a int, constraint c check (a > 0));
create table p2 (a int);
alter table p2 add constraint c check (a > 0) not valid;
create table k () inherits (p1, p2);
alter table k no inherit p1;
Table k comes back as NOT VALID, but it should be valid.
To preserve conislocal and convalidated, pg_dump need emit a
ALTER TABLE public.k VALIDATE CONSTRAINT c;
*after*
ALTER TABLE public.p2
ADD CONSTRAINT c CHECK ((a > 0)) NOT VALID;
Regarding pg_constraint.conislocal, we already have inconsistency, see
function flagInhAttrs.
/*
* For versions >18, we must print the not-null constraint locally
* for this table even if it isn't really locally defined, but is
* valid for the child and no parent has it as valid.
*/
if (fout->remoteVersion >= 180000 && allNotNullsInvalid)
tbinfo->notnull_islocal[j] = true;
Not preserving pg_constraint.conislocal in pg_dump would surely make
life easier.
Related thread:
https://www.postgresql.org/message-id/202504281450.4powv4cchler%40alvherre.pgsql
Now we kow the visible consequences: after pg_dump, ALTER TABLE DROP
CONSTRAINT will no longer cascade to the child table's constraint.
--
jian
https://www.enterprisedb.com/
