From: =?utf-8?q?=D0=94=D0=B0=D0=B2=D1=8B=D0=B4=D0=BE=D0=B2_=D0=92=D0=B8=D1=82=D0=B0=D0=BB=D0=B8=D0=B9?= <v.davydov@postgrespro.ru>
In-Reply-To: <a375c573-6c9b-4f0f-82c4-eb19cef2a4ca@iki.fi>
Content-Type: multipart/alternative; boundary="----=_=-_OpenGroupware_org_NGMime-1023771-1709648972.525257-3------"
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>
Date: Tue, 05 Mar 2024 17:29:32 +0300
Cc: pgsql-hackers@lists.postgresql.org
To: "Heikki Linnakangas" <hlinnaka@iki.fi>
MIME-Version: 1.0
Message-ID: <f9f1b-65e72c80-7-533dda00@76203995>
Subject: =?utf-8?q?Re=3A?= Slow catchup of 2PC (twophase) transactions on replica in 
 LR
User-Agent: SOGoMail 5.9.1
Archived-At: <https://www.postgresql.org/message-id/f9f1b-65e72c80-7-533dda00%4076203995>
Precedence: bulk

------=_=-_OpenGroupware_org_NGMime-1023771-1709648972.525257-3------
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Content-Length: 2341


Hi Heikki,

Thank you for the reply.

On Tuesday, March 05, 2024 12:05 MSK, Heikki Linnakangas <hlinnaka@iki.=
fi> wrote:
=C2=A0In a nutshell, this changes PREPARE TRANSACTION so that if
synchronous=5Fcommit is 'off', the PREPARE TRANSACTION is not fsync'd t=
o
disk. So if you crash after the PREPARE TRANSACTION has returned, the
transaction might be lost. I think that's completely unacceptable.=E2=80=
=8B=E2=80=8B=E2=80=8B=E2=80=8B=E2=80=8B
You are right, the prepared transaction might be lost after crash. The =
same may happen with regular transactions that are not fsync-ed on repl=
ica in logical replication by default. The subscription parameter synch=
ronous=5Fcommit is OFF by default. I'm not sure, is there some auto rec=
overy for regular transactions? I think, the main difference between th=
ese two cases - how to manually recover when some PREPARE TRANSACTION o=
r COMMIT PREPARED are lost. For regular transactions, some updates or d=
eletes in tables on replica may be enough to fix the problem. For twoph=
ase transactions, it may be harder to fix it by hands, but it is possib=
le, I believe. If you create a custom solution that is based on twophas=
e transactions (like multimaster) such auto recovery may happen automat=
ically. Another solution is to ignore errors on commit prepared if the =
corresponding prepared tx is missing. I don't know other risks that may=
 happen with async commit of twophase transactions.
=C2=A0If you're ok to lose the prepared state of twophase transactions =
on
crash, why don't you create the subscription with 'two=5Fphase=3Doff' t=
o
begin with?In usual work, the subscription has two=5Fphase =3D on. I ha=
ve to change this option at catchup stage only, but this parameter can =
not be altered. There was a patch proposal in past to implement alterin=
g of two=5Fphase option, but it was rejected. I think, the recreation o=
f the subscription with two=5Fphase =3D off will not work.

I believe, async commit for twophase transactions on catchup will signi=
ficantly improve the catchup performance. It is worth to think about su=
ch feature.

P.S. We might introduce a GUC option to allow async commit for twophase=
 transactions. By default, sync commit will be applied for twophase tra=
nsactions, as it is now.

With best regards,
Vitaly Davydov

------=_=-_OpenGroupware_org_NGMime-1023771-1709648972.525257-3------
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Content-Length: 2668

<html>Hi Heikki,<br /><br />Thank you for the reply.<br /><br />On Tues=
day, March 05, 2024 12:05 MSK, Heikki Linnakangas &lt;hlinnaka@iki.fi&g=
t; wrote:<br />&nbsp;<blockquote type=3D"cite" cite=3D"a375c573-6c9b-4f=
0f-82c4-eb19cef2a4ca@iki.fi">In a nutshell, this changes PREPARE TRANSA=
CTION so that if<br />synchronous=5Fcommit is 'off', the PREPARE TRANSA=
CTION is not fsync'd to<br />disk. So if you crash after the PREPARE TR=
ANSACTION has returned, the<br />transaction might be lost. I think tha=
t's completely unacceptable.=E2=80=8B=E2=80=8B=E2=80=8B=E2=80=8B=E2=80=8B=
</blockquote><br />You are right, the prepared transaction might be los=
t after crash. The same may happen with regular transactions that are n=
ot fsync-ed on replica in logical replication by default. The subscript=
ion parameter synchronous=5Fcommit is OFF by default. I'm not sure, is =
there some auto recovery for regular transactions? I think, the main di=
fference between these two cases - how to manually recover when some PR=
EPARE TRANSACTION or COMMIT PREPARED are lost. For regular transactions=
, some updates or deletes in tables on replica may be enough to fix the=
 problem. For twophase transactions, it may be harder to fix it by hand=
s, but it is possible, I believe. If you create a custom solution that =
is based on twophase transactions (like multimaster) such auto recovery=
 may happen automatically. Another solution is to ignore errors on comm=
it prepared if the corresponding prepared tx is missing. I don't know o=
ther risks that may happen with async commit of twophase transactions.<=
br />&nbsp;<blockquote type=3D"cite" cite=3D"a375c573-6c9b-4f0f-82c4-eb=
19cef2a4ca@iki.fi">If you're ok to lose the prepared state of twophase =
transactions on<br />crash, why don't you create the subscription with =
'two=5Fphase=3Doff' to<br />begin with?</blockquote>In usual work, the =
subscription has two=5Fphase =3D on. I have to change this option at ca=
tchup stage only, but this parameter can not be altered. There was a pa=
tch proposal in past to implement altering of two=5Fphase option, but i=
t was rejected. I think, the recreation of the subscription with two=5F=
phase =3D off will not work.<br /><br />I believe, async commit for two=
phase transactions on catchup will significantly improve the catchup pe=
rformance. It is worth to think about such feature.<br /><br />P.S. We =
might introduce a GUC option to allow async commit for twophase transac=
tions. By default, sync commit will be applied for twophase transaction=
s, as it is now.<br /><br />With best regards,<br />Vitaly Davydov</htm=
l>

------=_=-_OpenGroupware_org_NGMime-1023771-1709648972.525257-3--------