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 1pISmB-0007fr-QF for pgsql-bugs@arkaria.postgresql.org; Thu, 19 Jan 2023 11:08:11 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pISmA-0003em-Me for pgsql-bugs@arkaria.postgresql.org; Thu, 19 Jan 2023 11:08:10 +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 1pISmA-0003ec-Cl for pgsql-bugs@lists.postgresql.org; Thu, 19 Jan 2023 11:08:10 +0000 Received: from mail-oi1-x22a.google.com ([2607:f8b0:4864:20::22a]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pISm8-0000Td-6u for pgsql-bugs@lists.postgresql.org; Thu, 19 Jan 2023 11:08:09 +0000 Received: by mail-oi1-x22a.google.com with SMTP id d188so1393146oia.3 for ; Thu, 19 Jan 2023 03:08:08 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=NsnIM9arr1HToVUDuMiGCZ7IL4Cwre7pUwBipsYFjcg=; b=lc9chnNMw1eT1o5gC424YPKtcyGIlmTXdX+C0yxiVYnDQSpWR8euD879SGcMD8viSJ Pz6iZexZ/WUIsigl0zuR9+bQyCzKqRUdPOla63lfQ7u5Lw6yYgDxa11PEdAkmaoIzO6N EWossU19ccsZr71dFOEicBycWqYWVbDsIErSO6WnaQmwzi4KA9M2h+vOx3CiRbRO97yo L13hkeFt1HmmM2kJdzq7BraH5eK8Y+3XgCdhcDUam9OfRucgNtJr0rf/aE5CfUumYiUl RWMUOUoXm5FJ/Q24QA+rlmI6nICxOp/W2zuPsitIWnRdloBeHzxODdQYiPQ3LIeAFZn0 Wa0g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=NsnIM9arr1HToVUDuMiGCZ7IL4Cwre7pUwBipsYFjcg=; b=GuA6m+B9QwBtIkAJQQ9FTklXLz0Kci6ZRExTEhUbpeMlL3o8xkQtFHJdU+D6SjvEzC PLLC72fA1GtTytPz2a9tpRNmO0aCsrxczDD7zl1sGj5Jgyhwxi2vL2u+ahU/MKuJGa3J rRSxIpO/kdXmL+CNWF1b3vTgFlAN8GLy1mKjxtDSQ8NP+GUX+BMNSGWwpW4N7fXAB9nE 1U3G3cTUdE931AJ8tTBFxYsTMvSqiMMdQLgt7xTemL9BxBHuvUntGmpRmIRqxOQipXB1 5qMFot7/y6dl93CKW2Zo+nKYEleAtNX3dlu6MjMSpmci0nbPIl4wvZU3cYye6WaQMugp FvJw== X-Gm-Message-State: AFqh2ko18Urc37YZK3R0aMfPR58rAzW1zLMM5YiC6BkSoXEAAJHIifxd v/Ms4VxfMJ5T3PUVej/+U4E76l2YQPR0D/ke3Ug= X-Google-Smtp-Source: AMrXdXueChbHU+LKqRmiMa9oHWHj7kdi2kSuAS+ZNSBz8E0/idWa3+HRykLge/Xsg9Pe2z7xtj4BmbPwmN8UZTfvzRw= X-Received: by 2002:a05:6808:1818:b0:364:8ca8:7102 with SMTP id bh24-20020a056808181800b003648ca87102mr697725oib.261.1674126486491; Thu, 19 Jan 2023 03:08:06 -0800 (PST) MIME-Version: 1.0 References: <20230114160201.en5g5bna66x6lnuw@awork3.anarazel.de> <20230117200432.xaoenn7ni7srb2l2@awork3.anarazel.de> In-Reply-To: From: Amit Kapila Date: Thu, 19 Jan 2023 16:37:55 +0530 Message-ID: Subject: Re: DROP DATABASE deadlocks with logical replication worker in PG 15.1 To: "houzj.fnst@fujitsu.com" Cc: Lakshmi Narayanan Sreethar , "pgsql-bugs@lists.postgresql.org" , Andres Freund Content-Type: text/plain; charset="UTF-8" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Thu, Jan 19, 2023 at 3:46 PM houzj.fnst@fujitsu.com wrote: > > On Thursday, January 19, 2023 3:14 PM houzj.fnst@fujitsu.com wrote: > > On Wednesday, January 18, 2023 12:32 PM Amit Kapila > > wrote: > > > > > > On Wed, Jan 18, 2023 at 1:34 AM Andres Freund wrote: > > > > > > > > On 2023-01-17 06:23:45 +0530, Amit Kapila wrote: > > > > > > > > > There is an analysis of the test > > > > > failure in the email [2] which explains the race condition that > > > > > leads to test failure. Thinking again about the failure, I feel we > > > > > can instead change the failed test (t/004_sync.pl) to either > > > > > ensure that both the walsenders (corresponding to sync worker and > > > > > apply > > > > > worker) exits after dropping the subscription and before checking > > > > > the remaining slots on publisher or wait for slots to become zero > > > > > in the test. > > > > > > > > How about waiting for the table to start to be synced (and thus the > > > > slot to be > > > > created) before issuing the drop subscription? > > > > > > > > > > In this test [1], the initial sync fails due to a unique constraint > > > violation, so checking that the sync has started is a bit tricky. We > > > can probably check sync_error_count in pg_stat_subscription_stats to > > > ensure that sync has started to fail which will ideally ensure that > > > the sync has started. I am not sure this would be completely safe. The > > > other possible ways are (a) after creating a subscription, wait for > > > two slots to get created in the publisher, and then after dropping > > > subscription wait for slots to become zero on the publisher; (b) after dropping > > the subscription, wait for slots to become zero. > > > > > > I think one of (a) or (b) will work. > > > > I think in the mentioned testcase, the tablesync worker will keep restarting which > > means the table sync slot is also being dropped and re-created ... . So, (a) waiting > > for two slots to get created might not work as the slot will get dropped soon. I > > think (b) waiting for slot to become zero would be a simpler way to make the test > > stable. And here are the patches that tries to do it for all affected branches. > > When testing the patches on back-branches, I find that the reported deadlock > problem doesn't happen on PG14 and rather start from PG15 after commit 4eb2176 > which introduces the WaitForProcSignalBarrier logic in dropdb(). After this > commit, when executing the bug-reproduction sql script, the DROP DATABASE will > wait for the table sync worker to accept ProcSignalBarrier which can cause the > reported deadlock problem. > > But I think it's still worth fixing this on PG14 as well, as it would be better > to allow termination when executing command over network. > I also think that this should be backpatched in PG14 as well. -- With Regards, Amit Kapila.