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

Hi.
v2-0001 looks ok, but I added an errhint and a partitioned table test
case, see v4-0001.

Command ALTER COLUMN SET NOT NULL, which will call ATExecSetNotNull.
ATExecSetNotNull in some case will change existing pg_constraint.conislocal.
Similarly, perhaps we can let ALTER CONSTRAINT ENFORCED change the
conislocal too.
The attached alternative is let ALTER CONSTRAINT ENFORCED change the
pg_constraint.conislocal
for table inheritance (not partition inheritance).
------------
drop table if exists p3 cascade;
create table p3 (a int, constraint c check (a > 0) not enforced);
create table k3 () inherits (p3);
alter table k3 alter constraint c enforced;

The above command, the new pg_dump output is

CREATE TABLE public.p3 (a integer);
CREATE TABLE public.k3 ( CONSTRAINT c CHECK ((a > 0)) ) INHERITS (public.p3);
ALTER TABLE public.p3 ADD CONSTRAINT c CHECK ((a > 0)) NOT ENFORCED;
------------------------------------
The motivation for this alternative is restore performance.
when pg_dump emits ALTER TABLE ... ALTER CONSTRAINT ... ENFORCED.
This requires an extra full scan of the child table under an ACCESS
EXCLUSIVE lock.
For large tables, i am worried that this extra table scan may slow
down the pg_restore.

Let me know what you think of this alternative.

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