From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: hubert depesz lubaczewski <depesz@depesz.com>
Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Subject: Re: pg_restore deadlocks with itself
Date: Tue, 30 Aug 2022 11:06:34 +0200
Message-ID: <20220830090634.patldggt4zddwgu5@alvherre.pgsql> (raw)
In-Reply-To: <20220825124411.GA8257@depesz.com>
On 2022-Aug-25, hubert depesz lubaczewski wrote:
> Specifically, command to run it is:
> sudo -u postgres pg_restore -j 64 -d database -L /tmp/schema-post-data.nopkey.list /tmp/schema-post-data.dump
> 2022-08-24 20:01:04.466 UTC,"postgres","database",3343477,"[local]",630624ad.330475,42,"ALTER TABLE waiting",2022-08-24 13:16:29 UTC,21/1932,0,LOG,00000,"process 3343477 detected deadlock while waiting for ShareRowExclusiveLock on relation 742617610 of database 16641 after 1000.647 ms","Process holding the lock: 3587718. Wait queue: .",,,,,"ALTER TABLE ONLY some_schema.table_a_o
> ADD CONSTRAINT table_a_o_q_id_fk FOREIGN KEY (q_id) REFERENCES some_schema.table_q(id);
> ",,,"pg_restore","client backend",,3355460102417501954
>
>
> 2022-08-24 20:01:50.291 UTC,"postgres","database",3343477,"[local]",630624ad.330475,46,"ALTER TABLE waiting",2022-08-24 13:16:29 UTC,21/1933,0,LOG,00000,"process 3343477 detected deadlock while waiting for ShareRowExclusiveLock on relation 742617610 of database 16641 after 1000.030 ms","Process holding the lock: 3587718. Wait queue: .",,,,,"ALTER TABLE ONLY some_schema.table_a
> ADD CONSTRAINT fk_rails_46718e626a FOREIGN KEY (migrate_from_id) REFERENCES some_schema.table_q(id);
> ",,,"pg_restore","client backend",,-2548896815899838768
>
> Now, I know I can fix the situation by adding missing fkeys myself, but
> I don't think pg_restore should be putting itself in deadlock.
You're right, it shouldn't. Parallel restore shouldn't run DDL commands
in parallel that would deadlock, but I suppose there must be holes in
that.
What was process 3587718 doing at the time? Some DDL in
some_schema.table_q, I suspect, right? Are any of these tables
partitioned?
The "Wait queue: ." bit is ugly.
--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
"The problem with the facetime model is not just that it's demoralizing, but
that the people pretending to work interrupt the ones actually working."
(Paul Graham)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: alvherre@alvh.no-ip.org, depesz@depesz.com, pgsql-bugs@lists.postgresql.org
Subject: Re: pg_restore deadlocks with itself
In-Reply-To: <20220830090634.patldggt4zddwgu5@alvherre.pgsql>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox