[Tom Lane <tgl@sss.pgh.pa.us>]

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@alvh.no-ip.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.1743801143@sss.pgh.pa.us

Attachments:

restore-deadlock.sh (text/plain, 655B) download
psql postgres <<EOF
CREATE DATABASE src;
\c src

CREATE TABLE parent1 (
    id integer PRIMARY KEY,
    b text
)
PARTITION BY LIST (id);

CREATE TABLE c11 PARTITION OF parent1 FOR VALUES IN (1);
CREATE TABLE c12 PARTITION OF parent1 FOR VALUES IN (2);

CREATE TABLE parent2 (
    id integer PRIMARY KEY,
    ref integer REFERENCES parent1,
    b text
)
PARTITION BY LIST (id);

CREATE TABLE c21 PARTITION OF parent2 FOR VALUES IN (1);
CREATE TABLE c22 PARTITION OF parent2 FOR VALUES IN (2);

INSERT INTO parent1 VALUES(1, 'foo');
INSERT INTO parent2 VALUES(2, 1, 'bar');
EOF

pg_dump src -f src.dump -Fc
createdb target
pg_restore src.dump -d target -j10