On Thu, Apr 3, 2025 at 2:24 AM Alvaro Herrera <alvhe...@alvh.no-ip.org> wrote: > > create table singlepp (id bigint default 1) partition by list (id); > alter table singlepp add constraint dummy_constr not null id not valid; > create table singlepp_1 (id bigint default 1); > alter table singlepp_1 add constraint dummy_constr not null id; > alter table singlepp attach partition singlepp_1 for values in ('1'); > > Here, conislocal for the constraint on singlepp_1 is false. > > select conislocal from pg_constraint where conrelid = 'singlepp_1'::regclass; > conislocal > ──────────── > f > > if I run pg_dump and restore in a different database, it emits this: > > CREATE TABLE public.singlepp ( > id bigint DEFAULT 1 > ) > PARTITION BY LIST (id); > CREATE TABLE public.singlepp_1 ( > id bigint DEFAULT 1 CONSTRAINT dummy_constr NOT NULL > ); > ALTER TABLE ONLY public.singlepp ATTACH PARTITION public.singlepp_1 > FOR VALUES IN ('1'); > ALTER TABLE public.singlepp > ADD CONSTRAINT dummy_constr NOT NULL id NOT VALID; >
Thanks for mentioning flagInhAttrs! For table partitioning, the V6 pg_dump output is correct. conislocal's discrepancy in before and after pg_dump can be fixed(adjust) in AdjustNotNullInheritance. per above quoted example, The main idea is ALTER TABLE public.singlepp ADD CONSTRAINT dummy_constr NOT NULL id NOT VALID; will cascade to table singlepp_1 . However, since singlepp_1 already has a valid NOT NULL constraint, merging occurs. like, singlepp_1's coninhcount value increases from 0 to 1. while at it, we can also set conislocal to false. with the same idea, the pg_constraint.convalidated discrepancy before and after pg_dump also resolved. but we need to change the pg_dump output for table inheritance. for table inheritance: CREATE TABLE inhnn (a INTEGER); ALTER TABLE inhnn ADD CONSTRAINT cc not null a NOT VALID; CREATE TABLE inhnn_cc(a INTEGER) INHERITS(inhnn); the V6 output is CREATE TABLE public.inhnn (a integer); CREATE TABLE public.inhnn_cc ( a integer) INHERITS (public.inhnn); ALTER TABLE public.inhnn ADD CONSTRAINT cc NOT NULL a NOT VALID; we need change it to CREATE TABLE public.inhnn (a integer); CREATE TABLE public.inhnn_cc (a integer CONSTRAINT cc NOT NULL) INHERITS (public.inhnn); ALTER TABLE public.inhnn ADD CONSTRAINT cc NOT NULL a NOT VALID; so that after pg_dump we can still have a state where the parent's constraint is invalid and the child's is valid. summary: For parents invalid children valid cases, pg_dump's output changes the convalidate and conislocal column value. To resolve this issue: For table partitioning: V6 pg_dump output works fine, but need change function AdjustNotNullInheritance For table inheritance: need change pg_dump output, also change MergeWithExistingConstraint. needless to say, attach scratch96.sql is used to test pg_dump before and after the difference. you can compare V6 and my changes.
scratch96.sql
Description: application/sql
v6-0001-ensure-pg_dump-table-constraint-info-remain-th.no-cfbot
Description: Binary data