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

[Postgres all-rounder]

Hi Team,

*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.

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

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?

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.

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

[Postgres all-rounder]

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