MIME-Version: 1.0
References: <8fab8-65d74c80-1-2f28e880@39088166> <CAA4eK1KOs3s6syZqUgrd2WvjTz64SGf0ToZcRoPMCKKH+M0YFQ@mail.gmail.com>
 <99df2-65d8d200-1-63c26880@172687752> <CAA4eK1KtjjPxe5tjWeGNf5CfF9qfceVQCCC9XGsKzR-ifQD9uA@mail.gmail.com>
 <ba4b6-65ddc580-9-5c3e3080@134362348> <CAA4eK1J6wOMkDDt3iOs+JqkvQvuwSORosTpqdejgvXqFmxex=A@mail.gmail.com>
 <badcf-65dde500-3-24ba3c80@28790566> <cedbe-65e0c000-1-6db17700@133269862>
 <a375c573-6c9b-4f0f-82c4-eb19cef2a4ca@iki.fi> <f9f1b-65e72c80-7-533dda00@76203995>
 <CAFPTHDbU4EkVmKL66i5BzrmQ6LFftQPx_TZ1euKt9fvVzQDyjA@mail.gmail.com>
 <CAA4eK1+n7M2S1OpoGWDd+YZkDCuURMdVRbvP0eELQUvWgmDneg@mail.gmail.com>
 <CAFPTHDa=pJSZ_4dV5DPAOapRSgPcyyUTP0WzGY2Rz_D3-gwraw@mail.gmail.com>
 <CAA4eK1K1fSkeK=kc26G5cq87vQG4=1qs_b+no4+ep654SeBy1w@mail.gmail.com> <OSBPR01MB2552707A847936E6803CFAA5F5092@OSBPR01MB2552.jpnprd01.prod.outlook.com>
In-Reply-To: <OSBPR01MB2552707A847936E6803CFAA5F5092@OSBPR01MB2552.jpnprd01.prod.outlook.com>
From: Amit Kapila <amit.kapila16@gmail.com>
Date: Mon, 15 Apr 2024 15:16:53 +0530
Message-ID: <CAA4eK1KY=uwXXuVMtuNTYHGFbbXgDveoFoP3UbxNXqxCAx8GBQ@mail.gmail.com>
Subject: Re: Slow catchup of 2PC (twophase) transactions on replica in LR
To: "Hayato Kuroda (Fujitsu)" <kuroda.hayato@fujitsu.com>
Cc: Ajin Cherian <itsajin@gmail.com>, 
	=?UTF-8?B?0JTQsNCy0YvQtNC+0LIg0JLQuNGC0LDQu9C40Lk=?= <v.davydov@postgrespro.ru>, 
	Heikki Linnakangas <hlinnaka@iki.fi>, 
	"pgsql-hackers@lists.postgresql.org" <pgsql-hackers@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAA4eK1KY%3DuwXXuVMtuNTYHGFbbXgDveoFoP3UbxNXqxCAx8GBQ%40mail.gmail.com>
Precedence: bulk

On Mon, Apr 15, 2024 at 1:28=E2=80=AFPM Hayato Kuroda (Fujitsu)
<kuroda.hayato@fujitsu.com> wrote:
>
> > Vitaly, does the minimal solution provided by the proposed patch
> > (Allow to alter two_phase option of a subscriber provided no
> > uncommitted
> > prepared transactions are pending on that subscription.) address your u=
se case?
>
> I think we do not have to handle cases which there are prepared transacti=
ons on
> publisher/subscriber, as the first step. It leads additional complexity a=
nd we
> do not have smarter solutions, especially for problem 2.
> IIUC it meets the Vitaly's condition, right?
>
> > > 1. While toggling two_phase from true to false, we could probably get=
 a list of
> > prepared transactions for this subscriber id and rollback/abort the pre=
pared
> > transactions. This will allow the transactions to be re-applied like a =
normal
> > transaction when the commit comes. Alternatively, if this isn't appropr=
iate doing it
> > in the ALTER SUBSCRIPTION context, we could store the xids of all prepa=
red
> > transactions of this subscription in a list and when the corresponding =
xid is being
> > committed by the apply worker, prior to commit, we make sure the previo=
usly
> > prepared transaction is rolled back. But this would add the overhead of=
 checking
> > this list every time a transaction is committed by the apply worker.
> > >
> >
> > In the second solution, if you check at the time of commit whether
> > there exists a prior prepared transaction then won't we end up
> > applying the changes twice? I think we can first try to achieve it at
> > the time of Alter Subscription because the other solution can add
> > overhead at each commit?
>
> Yeah, at least the second solution might be problematic. I prototyped
> the first one and worked well. However, to make the feature more consiste=
nt,
> it is prohibit to exist prepared transactions on subscriber for now.
> We can ease based on the requirement.
>
> > > 2. No solution yet.
> > >
> >
> > One naive idea is that on the publisher we can remember whether the
> > prepare has been sent and if so then only send commit_prepared,
> > otherwise send the entire transaction. On the subscriber-side, we
> > somehow, need to ensure before applying the first change whether the
> > corresponding transaction is already prepared and if so then skip the
> > changes and just perform the commit prepared. One drawback of this
> > approach is that after restart, the prepare flag wouldn't be saved in
> > the memory and we end up sending the entire transaction again. One way
> > to avoid this overhead is that the publisher before sending the entire
> > transaction checks with subscriber whether it has a prepared
> > transaction corresponding to the current commit. I understand that
> > this is not a good idea even if it works but I don't have any better
> > ideas. What do you think?
>
> I considered but not sure it is good to add such mechanism. Your idea req=
uires
> additional wait-loop, which might lead bugs and unexpected behavior. And =
it may
> degrade the performance based on the network environment.
> As for the another solution (worker sends a list of prepared transactions=
), it
> is also not so good because list of prepared transactions may be huge.
>
> Based on above, I think we can reject the case for now.
>
> FYI - We also considered the idea which walsender waits until all prepare=
d transactions
> are resolved before decoding and sending changes, but it did not work wel=
l
> - the restarted walsender sent only COMMIT PREPARED record for transactio=
ns which
> have been prepared before disabling the subscription. This happened becau=
se
> 1) if the two_phase option of slots is false, the confirmed_flush can be =
ahead of
>    PREPARE record, and
> 2) after the altering and restarting, start_decoding_at becomes same as
>    confirmed_flush and records behind this won't be decoded.
>

I don't understand the exact problem you are facing. IIUC, if the
commit is after start_decoding_at point and prepare was before it, we
expect to send the entire transaction followed by a commit record. The
restart_lsn should be before the start of such a transaction and we
should have recorded the changes in the reorder buffer.

--=20
With Regards,
Amit Kapila.