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>
In-Reply-To: <CAFPTHDa=pJSZ_4dV5DPAOapRSgPcyyUTP0WzGY2Rz_D3-gwraw@mail.gmail.com>
From: Amit Kapila <amit.kapila16@gmail.com>
Date: Wed, 10 Apr 2024 16:48:26 +0530
Message-ID: <CAA4eK1K1fSkeK=kc26G5cq87vQG4=1qs_b+no4+ep654SeBy1w@mail.gmail.com>
Subject: Re: Slow catchup of 2PC (twophase) transactions on replica in LR
To: Ajin Cherian <itsajin@gmail.com>
Cc: =?UTF-8?B?0JTQsNCy0YvQtNC+0LIg0JLQuNGC0LDQu9C40Lk=?= <v.davydov@postgrespro.ru>, 
	Heikki Linnakangas <hlinnaka@iki.fi>, 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/CAA4eK1K1fSkeK%3Dkc26G5cq87vQG4%3D1qs_b%2Bno4%2Bep654SeBy1w%40mail.gmail.com>
Precedence: bulk

On Fri, Apr 5, 2024 at 4:59=E2=80=AFPM Ajin Cherian <itsajin@gmail.com> wro=
te:
>
> On Thu, Apr 4, 2024 at 4:38=E2=80=AFPM Amit Kapila <amit.kapila16@gmail.c=
om> wrote:
>>
>>
>> I think this would probably be better than the current situation but
>> can we think of a solution to allow toggling the value of two_phase
>> even when prepared transactions are present? Can you please summarize
>> the reason for the problems in doing that and the solutions, if any?
>>
>
>
> Updated the patch, as it wasn't addressing updating of two-phase in the r=
emote slot.
>

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 use c=
ase?

>  Currently the main issue that needs to be handled is the handling of pen=
ding prepared transactions while the two_phase is altered. I see 3 issues w=
ith the current approach.
>
> 1. Uncommitted prepared transactions when toggling two_phase from true to=
 false
> =E2=80=83=E2=80=83When two_phase was true, prepared transactions were dec=
oded at PREPARE time and send to the subscriber, which is then prepared on =
the subscriber with a new gid. Once the two_phase is toggled to false, then=
 the COMMIT PREPARED on the publisher is converted to commit and the entire=
 transaction is decoded and sent to the subscriber. This will   leave the p=
reviously prepared transaction pending.
>
> 2. Uncommitted prepared transactions when toggling two_phase form false t=
o true
> =E2=80=83=E2=80=83When two_phase was false, prepared transactions were ig=
nored and not decoded at PREPARE time on the publisher. Once the two_phase =
is toggled to true, the apply worker and the walsender are restarted and a =
replication is restarted from a new "start_decoding_at" LSN. Now, this new =
"start_decoding_at" could be past the LSN of the PREPARE record and if so, =
the PREPARE record is skipped and not send to the subscriber. Look at comme=
nts in DecodeTXNNeedSkip() for detail.  Later when the user issues COMMIT P=
REPARED, this is decoded and sent to the subscriber. but there is no prepar=
ed transaction on the subscriber, and this fails because the  corresponding=
 gid of the transaction couldn't be found.
>
> 3. While altering the two_phase of the subscription, it is required to al=
so alter the two_phase field of the slot on the primary. The subscription c=
annot remotely alter the two_phase option of the slot when the subscription=
 is  enabled, as the slot is owned by the walsender on the publisher side.
>

Thanks for summarizing the reasons for not allowing altering the
two_pc property for a subscription.

> Possible solutions for the 3 problems:
>
> 1. While toggling two_phase from true to false, we could probably get a l=
ist of prepared transactions for this subscriber id and rollback/abort the =
prepared transactions. This will allow the transactions to be re-applied li=
ke a normal transaction when the commit comes. Alternatively, if this isn't=
 appropriate doing it in the ALTER SUBSCRIPTION context, we could store the=
 xids of all prepared transactions of this subscription in a list and when =
the corresponding xid is being committed by the apply worker, prior to comm=
it, we make sure the previously prepared transaction is rolled back. But th=
is 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?

> 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?

> 3. We could mandate that the altering of two_phase state only be done aft=
er disabling the subscription, just like how it is handled for failover opt=
ion.
>

makes sense.

--=20
With Regards,
Amit Kapila.