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
database47159; 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
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
