Content-Type: text/plain;
	charset=us-ascii
Mime-Version: 1.0 (Mac OS X Mail 15.0 \(3693.60.0.1.1\))
Subject: Re: An attempt to avoid
 locally-committed-but-not-replicated-to-standby-transactions in synchronous
 replication
From: Andrey Borodin <x4mmm@yandex-team.ru>
In-Reply-To: 
 <CALj2ACWoB3k0kfjA7JxJgskVGGeE3jWzmGRPjP9QTRSCgSjhOg@mail.gmail.com>
Date: Mon, 9 May 2022 16:09:30 +0500
Cc: Laurenz Albe <laurenz.albe@cybertec.at>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
 SATYANARAYANA NARLAPURAM <satyanarlapuram@gmail.com>
Content-Transfer-Encoding: quoted-printable
Message-Id: <763B5AF0-1C9E-4796-9639-F969A2E66189@yandex-team.ru>
References: 
 <CALj2ACUrOB59QaE6=jF2cFAyv1MR7fzD8tr4YM5+OwEYG1SNzA@mail.gmail.com>
 <9290b55b6ae2b04e002ca9dadadd1cca09461482.camel@cybertec.at>
 <CALj2ACWoB3k0kfjA7JxJgskVGGeE3jWzmGRPjP9QTRSCgSjhOg@mail.gmail.com>
To: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/763B5AF0-1C9E-4796-9639-F969A2E66189%40yandex-team.ru>
Precedence: bulk


> On 9 May 2022, at 14:20, Bharath Rupireddy =
<bharath.rupireddyforpostgres@gmail.com> wrote:
>=20
> On Tue, Apr 26, 2022 at 11:57 AM Laurenz Albe =
<laurenz.albe@cybertec.at> wrote:
>>=20
>> While this may mitigate the problem, I don't think it will deal with
>> all the cases which could cause a transaction to end up committed =
locally,
>> but not on the synchronous standby.  I think that only using the full
>> power of two-phase commit can make this bulletproof.
>=20
> Not sure if it's recommended to use 2PC in postgres HA with sync
> replication where the documentation says that "PREPARE TRANSACTION"
> and other 2PC commands are "intended for use by external transaction
> management systems" and with explicit transactions. Whereas, the txns
> within a postgres HA with sync replication always don't have to be
> explicit txns. Am I missing something here?

COMMIT PREPARED needs to be replicated as well, thus encountering the =
very same problem as usual COMMIT: if done during failover it can be =
canceled and committed data can be wrongfully reported durably written. =
2PC is not a remedy to the fact that PG silently cancels awaiting of =
sync replication. The problem arise in presence of any "commit". And =
"commit" is there if transactions are there.

> On 9 May 2022, at 14:44, Dilip Kumar <dilipbalaut@gmail.com> wrote:
>=20
> IMHO, making it wait for some amount of time, based on GUC is not a
> complete solution.  It is just a hack to avoid the problem in some
> cases.

Disallowing cancelation of locally committed transactions is not a hack. =
It's removing of a hack that was erroneously installed to make backend =
responsible to Ctrl+C (or client side statement timeout).

> On 26 Apr 2022, at 11:26, Laurenz Albe <laurenz.albe@cybertec.at> =
wrote:
>=20
> Is it worth adding additional complexity that is not a complete =
solution?

Its not additional complexity. It is removing additional complexity that =
made sync rep interruptible. (But I'm surely talking not about GUCs like =
synchronous_replication_naptime_before_cancel - wait of sync rep must be =
indefinite until synchrous_commit\synchronous_standby_names are =
satisfied )

And yes, we need additional complexity - but in some other place. =
Transaction can also be locally committed in presence of a server crash. =
But this another difficult problem. Crashed server must not allow data =
queries until LSN of timeline end is successfully replicated to =
synchronous_standby_names.

Best regards, Andrey Borodin.=