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

On 2025-Apr-02, jian he wrote:

> we need special code for handing parent is invalid, child is valid
> (table inheritance or partitioning).

Hmmmm. I'm going to focus on this case, which is the simplest one we
care about (no multi-level hierarchy, only not null constraint):

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;

If you restore this, you'll get the exact same database. The only
difference is that conislocal is now true. If you dump the new
database, it'll produce exactly the same output as above. You can also
drop the constraint, you'll end up with identical state; or not drop it
an detach the partition instead, and you'll end up with identical state.

If you do pg_upgrade of this cluster, these two databases are identical
to the original ones (down to the conislocal setting on each).

So I'm having a really hard time finding a reason to care about this
conislocal difference.

> Therefore another ExecuteSqlQuery query execution is needed.
> it may cause performance problems for pg_dump, I guess.
> There may be other simple ways to do it.

Yeah, I'm 100% sure we don't want *any* slowdown to get an effect that's
virtually indetectable from the user point of view.

I think one way to fix it might be to make flagInhAttrs do something so
that the conislocal flag is created the other way. But frankly, I see
little reason to spend time on it.

Do you see anything different?

--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
"You don't solve a bad join with SELECT DISTINCT" #CupsOfFail
https://twitter.com/connor_mc_d/status/1431240081726115845