Message-ID: <9290b55b6ae2b04e002ca9dadadd1cca09461482.camel@cybertec.at>
Subject: Re: An attempt to avoid
 locally-committed-but-not-replicated-to-standby-transactions in synchronous
 replication
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>, PostgreSQL
	Hackers <pgsql-hackers@lists.postgresql.org>
Cc: SATYANARAYANA NARLAPURAM <satyanarlapuram@gmail.com>
Date: Tue, 26 Apr 2022 08:26:59 +0200
In-Reply-To: <CALj2ACUrOB59QaE6=jF2cFAyv1MR7fzD8tr4YM5+OwEYG1SNzA@mail.gmail.com>
References: 
	<CALj2ACUrOB59QaE6=jF2cFAyv1MR7fzD8tr4YM5+OwEYG1SNzA@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
User-Agent: Evolution 3.40.4 (3.40.4-5.fc34) 
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/9290b55b6ae2b04e002ca9dadadd1cca09461482.camel%40cybertec.at>
Precedence: bulk

On Mon, 2022-04-25 at 19:51 +0530, Bharath Rupireddy wrote:
> With synchronous replication typically all the transactions (txns)
> first locally get committed, then streamed to the sync standbys and
> the backend that generated the transaction will wait for ack from sync
> standbys. While waiting for ack, it may happen that the query or the
> txn gets canceled (QueryCancelPending is true) or the waiting backend
> is asked to exit (ProcDiePending is true). In either of these cases,
> the wait for ack gets canceled and leaves the txn in an inconsistent
> state [...]
> 
> Here's a proposal (mentioned previously by Satya [1]) to avoid the
> above problems:
> 1) Wait a configurable amount of time before canceling the sync
> replication by the backends i.e. delay processing of
> QueryCancelPending and ProcDiePending in Introduced a new timeout GUC
> synchronous_replication_naptime_before_cancel, when set, it will let
> the backends wait for the ack before canceling the synchronous
> replication so that the transaction can be available in sync standbys
> as well.
> 2) Wait for sync standbys to catch up upon restart after the crash or
> in the next txn after the old locally committed txn was canceled.

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.

Is it worth adding additional complexity that is not a complete solution?

Yours,
Laurenz Albe