Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps
 (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <pgsql-bugs-owner+archive@lists.postgresql.org>)
	id 1oSy19-0005fD-T3
	for pgsql-bugs@arkaria.postgresql.org; Tue, 30 Aug 2022 09:58:47 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.92)
	(envelope-from <pgsql-bugs-owner+archive@lists.postgresql.org>)
	id 1oSy17-0007LS-4S
	for pgsql-bugs@arkaria.postgresql.org; Tue, 30 Aug 2022 09:58:45 +0000
Received: from makus.postgresql.org ([2001:4800:3e1:1::229])
	by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <depesz@depesz.com>)
	id 1oSy16-0007LJ-So
	for pgsql-bugs@lists.postgresql.org; Tue, 30 Aug 2022 09:58:44 +0000
Received: from lana.depesz.com ([88.198.49.178] helo=depesz.com)
	by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <depesz@depesz.com>)
	id 1oSy14-0000Lf-Ff
	for pgsql-bugs@lists.postgresql.org; Tue, 30 Aug 2022 09:58:43 +0000
DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=depesz.com;
	 s=20170201; h=In-Reply-To:Content-Type:MIME-Version:References:Reply-To:
	Message-ID:Subject:Cc:To:Sender:From:Date:Content-Transfer-Encoding:
	Content-ID:Content-Description:Resent-Date:Resent-From:Resent-Sender:
	Resent-To:Resent-Cc:Resent-Message-ID:List-Id:List-Help:List-Unsubscribe:
	List-Subscribe:List-Post:List-Owner:List-Archive;
	bh=DWRKpYMgGw3P5e+EOXO06v2wGZbsTaw9+xljmWb4g8M=; b=pmGWrRmQmeDXQhex4axXsApy+H
	tBK37NyiRzwRomLstG59tkOu1SKR/u8XzDDKSV7aNvb7GckpJSy+VkwK3w3WzpVysQ5l4yRaK8/87
	jhCEAjcY9t3ddPFCtcGDdmUCPxf/S6/lCkaDTD0n/1Zpp2jxyUscV7U2+pbgGQ1K4xAc=;
Received: from lana.depesz.com ([88.198.49.178] helo=depesz.com)
	by depesz.com with esmtpa (Exim 4.92)
	(envelope-from <depesz@depesz.com>)
	id 1oSy12-0003x8-Cu; Tue, 30 Aug 2022 11:58:40 +0200
Date: Tue, 30 Aug 2022 11:58:40 +0200
From: hubert depesz lubaczewski <depesz@depesz.com>
Sender: 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
Message-ID: <20220830095840.GA1661@depesz.com>
Reply-To: depesz@depesz.com
References: <20220830091622.GA29531@depesz.com>
 <20220830092937.5mp5suz2uqzpnrfw@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
In-Reply-To: <20220830092937.5mp5suz2uqzpnrfw@alvherre.pgsql>
User-Agent: Mutt/1.10.1 (2018-07-13)
List-Id: <pgsql-bugs.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-bugs@lists.postgresql.org>
List-Owner: <mailto:pgsql-bugs-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-bugs>
Archived-At: 
 <https://www.postgresql.org/message-id/20220830095840.GA1661%40depesz.com>
Precedence: bulk

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