From: Amit Kapila <amit.kapila16@gmail.com>
To: houzj.fnst@fujitsu.com <houzj.fnst@fujitsu.com>
Cc: Lakshmi Narayanan Sreethar <lakshmi@timescale.com>
Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Cc: Andres Freund <andres@anarazel.de>
Subject: Re: DROP DATABASE deadlocks with logical replication worker in PG 15.1
Date: Thu, 19 Jan 2023 16:37:55 +0530
Message-ID: <CAA4eK1+y2LdoG0dm_yAEb_p8Uik5eDZGzagMS9O8gdkrS8g7MA@mail.gmail.com> (raw)
In-Reply-To: <OS0PR01MB57161F9E9C15E73012FC192C94C49@OS0PR01MB5716.jpnprd01.prod.outlook.com>
References: <CA+kvmZELXQ4ZD3U=XCXuG3KvFgkuPoN1QrEj8c-rMRodrLOnsg@mail.gmail.com>
<20230114160201.en5g5bna66x6lnuw@awork3.anarazel.de>
<CAA4eK1L5c+ZcK72evGxodq3zLje=Qv-t2Qi1GcAmKxnm5SQhYQ@mail.gmail.com>
<CAA4eK1+s1B7-UD+JG_-ZVMag8DeufQ0oshS5RGaZ-LY9v7bVuQ@mail.gmail.com>
<20230117200432.xaoenn7ni7srb2l2@awork3.anarazel.de>
<CAA4eK1K+mpN-nz4j2WobyFjJAtLzV2pzjb_QZf0yATjVM6dOtQ@mail.gmail.com>
<OS0PR01MB571678C898EA980B444BC7CE94C49@OS0PR01MB5716.jpnprd01.prod.outlook.com>
<OS0PR01MB57161F9E9C15E73012FC192C94C49@OS0PR01MB5716.jpnprd01.prod.outlook.com>
On Thu, Jan 19, 2023 at 3:46 PM houzj.fnst@fujitsu.com
<houzj.fnst@fujitsu.com> wrote:
>
> On Thursday, January 19, 2023 3:14 PM houzj.fnst@fujitsu.com <houzj.fnst@fujitsu.com> wrote:
> > On Wednesday, January 18, 2023 12:32 PM Amit Kapila
> > <amit.kapila16@gmail.com> wrote:
> > >
> > > On Wed, Jan 18, 2023 at 1:34 AM Andres Freund <andres@anarazel.de> 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.
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-bugs@postgresql.org
Cc: amit.kapila16@gmail.com, houzj.fnst@fujitsu.com, lakshmi@timescale.com, andres@anarazel.de
Subject: Re: DROP DATABASE deadlocks with logical replication worker in PG 15.1
In-Reply-To: <CAA4eK1+y2LdoG0dm_yAEb_p8Uik5eDZGzagMS9O8gdkrS8g7MA@mail.gmail.com>
References: <CA+kvmZELXQ4ZD3U=XCXuG3KvFgkuPoN1QrEj8c-rMRodrLOnsg@mail.gmail.com> <20230114160201.en5g5bna66x6lnuw@awork3.anarazel.de> <CAA4eK1L5c+ZcK72evGxodq3zLje=Qv-t2Qi1GcAmKxnm5SQhYQ@mail.gmail.com> <CAA4eK1+s1B7-UD+JG_-ZVMag8DeufQ0oshS5RGaZ-LY9v7bVuQ@mail.gmail.com> <20230117200432.xaoenn7ni7srb2l2@awork3.anarazel.de> <CAA4eK1K+mpN-nz4j2WobyFjJAtLzV2pzjb_QZf0yATjVM6dOtQ@mail.gmail.com> <OS0PR01MB571678C898EA980B444BC7CE94C49@OS0PR01MB5716.jpnprd01.prod.outlook.com> <OS0PR01MB57161F9E9C15E73012FC192C94C49@OS0PR01MB5716.jpnprd01.prod.outlook.com> <CAA4eK1+y2LdoG0dm_yAEb_p8Uik5eDZGzagMS9O8gdkrS8g7MA@mail.gmail.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