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://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
