Re: Support NOT VALID / VALIDATE constraint options for named NOT NULL constraints

On Thu, Apr 3, 2025 at 2:24 AM Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)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.