From: hubert depesz lubaczewski <depesz@depesz.com>
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Subject: Re: pg_restore deadlocks with itself
Date: Tue, 30 Aug 2022 11:58:40 +0200
Message-ID: <20220830095840.GA1661@depesz.com> (raw)
In-Reply-To: <20220830092937.5mp5suz2uqzpnrfw@alvherre.pgsql>
References: <20220830091622.GA29531@depesz.com>
<20220830092937.5mp5suz2uqzpnrfw@alvherre.pgsql>
On Tue, Aug 30, 2022 at 11:29:37AM +0200, Alvaro Herrera wrote:
> On 2022-Aug-30, hubert depesz lubaczewski wrote:
>
> > 2022-08-24 20:01:04.466 UTC,"postgres","database",3343477,"[local]",630624ad.330475,43,"ALTER TABLE",2022-08-24 13:16:29 UTC,21/1932,0,ERROR,40P01,"deadlock detected","Process 3343477 waits for ShareRowExclusiveLock on relation 742617610 of database 16641; blocked by process 3587718.
> > Process 3587718 waits for RowExclusiveLock on relation 742615338 of database 16641; blocked by process 3343477.
> > Process 3343477: ALTER TABLE ONLY some_schema.table_ao
> > ADD CONSTRAINT table_ao_q_id_fk FOREIGN KEY (q_id) REFERENCES some_schema.table_q(id);
> >
> >
> >
> > Process 3587718: <command string not enabled>","See server log for query details.",,,,"ALTER TABLE ONLY some_schema.table_ao
> > ADD CONSTRAINT table_ao_q_id_fk FOREIGN KEY (q_id) REFERENCES some_schema.table_q(id);
> >
> >
> > ",,,"pg_restore","client backend",,3355460102417501954
> >
> > Which is weird, as it seems that they both were trying to do the same fkey?!
> >
> > Neither table_ao nor table_q are partitioned, and used restore-list contained this fkey only once.
>
> Hmm, you said table_a_o and table_a first, but now you say table_ao. I
> suspect that's just a typo resulting from anonymizing table names. Right?
>
> OK, so no partitioning. But are there any inheritance relationship
> between these tables?
There were two deadlocks, I might have copy/pasted info from another
case.
These are *all* deadlock related messages from the log, all where
anonymized using regexps, no manual intervention, so the changes should
be consistent.
======================================================
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_ao
ADD CONSTRAINT table_ao_q_id_fk FOREIGN KEY (q_id) REFERENCES some_schema.table_q(id);
",,,"pg_restore","client backend",,3355460102417501954
======================================================
2022-08-24 20:01:04.466 UTC,"postgres","database",3343477,"[local]",630624ad.330475,43,"ALTER TABLE",2022-08-24 13:16:29 UTC,21/1932,0,ERROR,40P01,"deadlock detected","Process 3343477 waits for ShareRowExclusiveLock on relation 742617610 of database 16641; blocked by process 3587718.
Process 3587718 waits for RowExclusiveLock on relation 742615338 of database 16641; blocked by process 3343477.
Process 3343477: ALTER TABLE ONLY some_schema.table_ao
ADD CONSTRAINT table_ao_q_id_fk FOREIGN KEY (q_id) REFERENCES some_schema.table_q(id);
Process 3587718: <command string not enabled>","See server log for query details.",,,,"ALTER TABLE ONLY some_schema.table_ao
ADD CONSTRAINT table_ao_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
======================================================
2022-08-24 20:01:50.291 UTC,"postgres","database",3343477,"[local]",630624ad.330475,47,"ALTER TABLE",2022-08-24 13:16:29 UTC,21/1933,0,ERROR,40P01,"deadlock detected","Process 3343477 waits for ShareRowExclusiveLock on relation 742617610 of database 16641; blocked by process 3587718.
Process 3587718 waits for RowExclusiveLock on relation 742615348 of database 16641; blocked by process 3343477.
Process 3343477: ALTER TABLE ONLY some_schema.table_a
ADD CONSTRAINT fk_rails_46718e626a FOREIGN KEY (migrate_from_id) REFERENCES some_schema.table_q(id);
Process 3587718: <command string not enabled>","See server log for query details.",,,,"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
======================================================
As for inheritance - none of table_a, table_ao, table_q has any
inheritance. There are tables with inheritance in this db, but not
these.
It is, though, possible that one of table_a/ao/q has fkey pointing to
some inherited table, or some inherited table has fkey to _a/_ao/_q
- would that matter? i can dig details if it would be interesting.
Best regards,
depesz
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: depesz@depesz.com, alvherre@alvh.no-ip.org, pgsql-bugs@lists.postgresql.org
Subject: Re: pg_restore deadlocks with itself
In-Reply-To: <20220830095840.GA1661@depesz.com>
* 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