pg_restore causing deadlocks on partitioned tables

From: Domagoj Smoljanovic <domagoj(dot)smoljanovic(at)oradian(dot)com>
To: "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject: pg_restore causing deadlocks on partitioned tables
Date: 2020-09-14 12:27:13
Message-ID: VI1PR03MB31670CA1BD9625C3A8C5DD05EB230@VI1PR03MB3167.eurprd03.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi all.

I tried searching for the response to this but couldn’t find any. Tried also posting to general but got no love there.

I have pg_restore running in parallel (3 or more) and processing large amount of data that is in partitioned tables. However it seems that sometime deadlock appears when one process is trying to process primary key on parent table while data still hasn’t been loaded into partitions. And acquires Exclusive Lock on the whole table. Then another process comes and tries to load one of the partitions with SharedLock but it fails.

This of course doesn’t happen always; depending on the course of actions of the pg_restore. But often enough to cause frustration.

Process 15858 waits for AccessShareLock on relation 233358134 of database 233346697; blocked by process 15861.
Process 15861 waits for AccessExclusiveLock on relation 233374757 of database 233346697; blocked by process 15858.
Process 15858: TRUNCATE TABLE ONLY myschema."myTable:2020-09-01";
Process 15861: ALTER TABLE ONLY myschema."myTable" ADD CONSTRAINT "pk_myTable" PRIMARY KEY ("ID", date);

Should this be treated as a bug or am I doing something wrong?

Disclamer: --load-via-partition-root was NOT used. Meaning that warning from the pg_dump documentation should not be applicable 😊

Thanx,
Domagoj

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Amit Kapila 2020-09-14 12:37:09 Re: Fix for parallel BTree initialization bug
Previous Message Daniel Gustafsson 2020-09-14 12:02:10 Re: Use incremental sort paths for window functions