RE: pg_restore causing deadlocks on partitioned tables

Forgot to mention the versions:
pg_restore (PostgreSQL) 12.4
source/ destination databases also 12.4

D.

-----Original Message-----
From: Alvaro Herrera <alvherre@2ndquadrant.com> 
Sent: 14. rujna 2020. 16:40
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Domagoj Smoljanovic <domagoj.smoljanovic@oradian.com>; pgsql-hackers@postgresql.org
Subject: Re: pg_restore causing deadlocks on partitioned tables

On 2020-Sep-14, Tom Lane wrote:

> Domagoj Smoljanovic <domagoj.smoljanovic@oradian.com> writes:
> > 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
tablewhile data still hasn’t been loaded into partitions. And acquires Exclusive Lock on the whole table. Then another
processcomes 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
causefrustration.
 
> 
> > 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);
> 
> Hm, this seems related to 2ba5b2db7, but not the same thing.
> Alvaro, any thoughts?

So apparently when we go to restore the table data for the partition, the TRUNCATE deadlocks with the PK addition ...
that'spretty odd; shouldn't the constraint restore have waited until the data had been fully loaded?
 

-- 
Álvaro Herrera
https://eur01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.2ndquadrant.com%2F&data=01%7C01%7Cdomagoj.smoljanovic%40oradian.com%7Cf9054c64e75a49adac3308d858bc1423%7Cc3d7e30ad09240c8b35c54a27682c60d%7C0&sdata=9pphCt1EzkEzrCuCg8CLdRywknjNiG6WLfRhR4T7qPQ%3D&reserved=0
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services