Re: synchronous_commit= remote_apply | "The transaction has already committed locally..."

Hi Laurenz,

Thank you for the quick response.

Could you please point me to the link where the "two-phase commit" approach
is being discussed.
I can track it for my reference.

On Fri, Jun 23, 2023 at 3:26 PM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
wrote:

> On Fri, 2023-06-23 at 15:05 +0530, Postgres all-rounder wrote:
> > Context: We have faced a network isolation and ended-up with locally
> committed data on the
> > old primary database server as one of the tools that is in-place for HA
> decided to promote
> > one of the SYNC standby servers. As the PostgreSQL won't provide a HA
> solution as in-built,
> > I would like to just confirm on the behaviour of core parameter
> synchronous_commit= remote_apply.
> >
> > As per the documentation the PRIMARY database server will NOT commit
> unless
> > the SYNC standby acknowledges that it received the commit record of
> the transaction
> > and applied it, so that it has become visible to queries on the
> standby(s), and also written
> > to durable storage on the standbys.
>
> That's not true. The primary will commit locally, but wait for the
> synchronous standby
> servers before it reports success to the client.
>
> > However, during the network outage or few scenarios where the current
> primary is waiting
> > for the SYNC to acknowledge and when the application sends a cancel
> signal [even control +c
> > from a PSQL session which inserted data] then we see locally committed
> data on the primary
> > database server.
> >
> > "The transaction has already committed locally, but might not have been
> replicated to the standby."
> >
> > 1. It appears to be a known behaviour, however wanted to understand, is
> this considered as an
> > expected behaviour or limitation with the architecture
>
> This is expected behavior AND a limitation of PostgreSQL.
>
> > 2. Any known future plans in the backlog to change the behaviour in
> > such a way PRIMARY won't have the LOCALLY commit data which is NOT
> received and acknowledged
> > by a SYNC standby when synchronous_commit= remote_apply is used?
>
> There have been efforts to use two-phase commit, but that would require
> PostgreSQL to
> have its own distributed transaction manager.
>
> > 3. If the information is available in the document that primary database
> can have locally
> > committed data when it is waiting on SYNC and receive the cancel signal
> from the application,
> > it can be helpful.
>
> I don't think that's anywhere in the documentation.
>
> Yours,
> Laurenz Albe
>