Forgot to mention the versions:
pg_restore (PostgreSQL) 12.4
source/ destination databases also 12.4
D.
-----Original Message-----
From: Alvaro Herrera <alvhe...@2ndquadrant.com>
Sent: 14. rujna 2020. 16:40
To: Tom Lane <t...@sss.pgh.pa.us>
Cc: Domagoj Smoljanovic <domagoj.smoljano...@oradian.com>;
pgsql-hack...@postgresql.org
Subject: Re: pg_restore causing deadlocks on partitioned tables
On 2020-Sep-14, Tom Lane wrote:
> Domagoj Smoljanovic <domagoj.smoljano...@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 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);
>
> 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's pretty 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
