pg_restore causing deadlocks on partitioned tables
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
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
Sent: 14. rujna 2020. 16:40
To: Tom Lane
Cc: Domagoj Smoljanovic ;
pgsql-hack...@postgresql.org
Subject: Re: pg_restore causing deadlocks on partitioned tables
On 2020-Sep-14, Tom Lane wrote:
> Domagoj Smoljanovic 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
RE: pg_restore causing deadlocks on partitioned tables
Thanx Tom and Amit for the effort. Looking forward to try it out. D. -Original Message- From: Tom Lane Sent: 16. rujna 2020. 20:41 To: Amit Langote Cc: Alvaro Herrera ; Domagoj Smoljanovic ; pgsql-hack...@postgresql.org Subject: Re: pg_restore causing deadlocks on partitioned tables Amit Langote writes: > Updated patch attached. Pushed with a little bit of fooling about. After looking at the git history, I saw that the Assert we were wondering about used to be just "Assert(constr)", and there were not run-time checks on whether constr is null. That was changed when f0e44751d added partition constraint checking into ExecConstraints' responsibilities. At some later point that code was removed from ExecConstraints, but we failed to undo the other changes in ExecConstraints, leaving it looking pretty silly. So I reverted this to the way it was, with just an Assert and no regular checks. I also did a bit more work on the comments. (Speaking of which, is there a better place to put the commentary you removed from InitResultRelInfo? It was surely wildly out of place there, but I'm wondering if maybe we have a README that should cover it.) I pushed this to HEAD only, and the other patch as far back as v12, so we will have a solution to the deadlock problem in v12. regards, tom lane
