Re: not null constraints, again

The attached script simply creates two partitioned tables that
are connected by a foreign key constraint, then pg_dumps that
setup and tries to do a parallel restore. This works up until

14e87ffa5c543b5f30ead7413084c25f7735039f is the first bad commit
commit 14e87ffa5c543b5f30ead7413084c25f7735039f
Author: Álvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>
Date: Fri Nov 8 13:28:48 2024 +0100

Add pg_constraint rows for not-null constraints

Since that commit, it fails every time (for me, anyway, on a couple
of different machines) with a deadlock error, typically between
ALTER ADD PRIMARY KEY and one of the table COPY commands:

2025-04-14 12:54:49.892 EDT [1278062] ERROR: deadlock detected
2025-04-14 12:54:49.892 EDT [1278062] DETAIL: Process 1278062 waits for AccessExclusiveLock on relation 47164 of database 47159; blocked by process 1278059.
Process 1278059 waits for AccessShareLock on relation 47160 of database 47159; blocked by process 1278062.
Process 1278062: ALTER TABLE ONLY public.parent1
ADD CONSTRAINT parent1_pkey PRIMARY KEY (id);
Process 1278059: COPY public.c11 (id, b) FROM stdin;

I stumbled across this result after wondering why the repro
I'd devised at [1] didn't fail in v17.

The patch I propose there seems to prevent this, but I wonder if we
shouldn't look closer into why it's failing in the first place.
I would not have expected that adding pg_constraint rows implies
stronger locks than what ALTER ADD PRIMARY KEY was using before,
and I suspect that doing so will cause more problems than just
breaking parallel restore.

regards, tom lane

[1] https://www.postgresql.org/message-id/flat/2045026(dot)1743801143(at)sss(dot)pgh(dot)pa(dot)us