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 ) id 1oSxYm-0004aI-Ej for pgsql-bugs@arkaria.postgresql.org; Tue, 30 Aug 2022 09:29:28 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oSxYk-0001C5-Lo for pgsql-bugs@arkaria.postgresql.org; Tue, 30 Aug 2022 09:29:26 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oSxYk-0001Bv-E0 for pgsql-bugs@lists.postgresql.org; Tue, 30 Aug 2022 09:29:26 +0000 Received: from out2-smtp.messagingengine.com ([66.111.4.26]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oSxYh-0006CY-Ma for pgsql-bugs@lists.postgresql.org; Tue, 30 Aug 2022 09:29:26 +0000 Received: from compute2.internal (compute2.nyi.internal [10.202.2.46]) by mailout.nyi.internal (Postfix) with ESMTP id A5B355C0179; Tue, 30 Aug 2022 05:29:22 -0400 (EDT) Received: from mailfrontend2 ([10.202.2.163]) by compute2.internal (MEProxy); Tue, 30 Aug 2022 05:29:22 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:cc:content-transfer-encoding :content-type:date:date:feedback-id:feedback-id:from:from :in-reply-to:in-reply-to:message-id:mime-version:reply-to:sender :subject:subject:to:to:x-me-proxy:x-me-proxy:x-me-sender :x-me-sender:x-sasl-enc; s=fm1; t=1661851762; x=1661938162; bh=d usMbcl4RgiCswJZKlfyX4ze6NP3eSPathV2ttyubxQ=; b=4dqEoQxc45BWr8TeI tINVbsaYpKswliRBa5ia80maTZNUQrpDvFogWLoiSqah3fIXMeOVkojtnh/s+MA0 UT7GbkGI7qaazEAEP/fpazCmw0KLX2yLrwWb9nB3iGxVSoDWteLykTG03+Y3h3nY DlRfBARpt5yG+qZyz//BbDHMzMJcbQ+ApaKqvkuSDyZScitslOADCNCcnroN6mKb IS5ZQCjF4yNUP4UiW2Ky4BZLNtBGj2IHTGZx5G8RN1iuGXfg7z6+SrWhbxa1Vsvz pX6Ji4KUL/Q9VBpcNWSy10zYZLWVcTKzmTrmdQRgyfnbJeUkqL+qx3+V1ULRjLra UFtQw== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvfedrvdekfedgudejucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucesvcftvggtihhpihgvnhhtshculddquddttddmne cujfgurhepfffhvfevuffkgggtugfgjgesthekredttddtjeenucfhrhhomheptehlvhgr rhhoucfjvghrrhgvrhgruceorghlvhhhvghrrhgvsegrlhhvhhdrnhhoqdhiphdrohhrgh eqnecuggftrfgrthhtvghrnhepvdektdffudfftdffffehfffhjeejhffgieeuueekjeek fffgudffhfduffffueevnecuffhomhgrihhnpegvnhhtvghrphhrihhsvggusgdrtghomh enucevlhhushhtvghrufhiiigvpedtnecurfgrrhgrmhepmhgrihhlfhhrohhmpegrlhhv hhgvrhhrvgesrghlvhhhrdhnohdqihhprdhorhhg X-ME-Proxy: Feedback-ID: ia2694551:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Tue, 30 Aug 2022 05:29:22 -0400 (EDT) Received: by perhan.alvh.no-ip.org (Postfix, from userid 1000) id 75AF9378; Tue, 30 Aug 2022 11:29:37 +0200 (CEST) Date: Tue, 30 Aug 2022 11:29:37 +0200 From: Alvaro Herrera To: hubert depesz lubaczewski Cc: PostgreSQL mailing lists Subject: Re: pg_restore deadlocks with itself Message-ID: <20220830092937.5mp5suz2uqzpnrfw@alvherre.pgsql> MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: <20220830091622.GA29531@depesz.com> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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: ","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? -- Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/ "No es bueno caminar con un hombre muerto"