Thread: synchronous_commit= remote_apply | "The transaction has already committed locally..."
synchronous_commit= remote_apply | "The transaction has already committed locally..."
From
Postgres all-rounder
Date:
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..."
From
Laurenz Albe
Date:
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
Re: synchronous_commit= remote_apply | "The transaction has already committed locally..."
From
Postgres all-rounder
Date:
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.albe@cybertec.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
Re: synchronous_commit= remote_apply | "The transaction has already committed locally..."
From
Laurenz Albe
Date:
On Fri, 2023-06-23 at 16:23 +0530, Postgres all-rounder wrote: > Could you please point me to the link where the "two-phase commit" approach is being discussed. > I can track it for my reference. I looked, and didn't find it. I must have mis-remembered. There is this proposal: https://www.postgresql.org/message-id/flat/CALj2ACUrOB59QaE6%3DjF2cFAyv1MR7fzD8tr4YM5%2BOwEYG1SNzA%40mail.gmail.com Yours, Laurenz Albe