From: Bruce Momjian <bruce@momjian.us>
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: P C <puravc@gmail.com>
Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: Synchronous Replication: Where is data visible first?
Date: Fri, 12 Jan 2024 17:43:57 -0500
Message-ID: <ZaHArQHT6dRnDm4A@momjian.us> (raw)
In-Reply-To: <CAKFQuwYUpW=LVcmjte1tqS9OhoskvFLPwLoxoMtnw8o5tW4UOw@mail.gmail.com>
References: <CADrzpjH8A_bOVU4vTwtDanrCuE5wt63NRcJ5QGQZZeLmaBuOgQ@mail.gmail.com>
<CAKFQuwYUpW=LVcmjte1tqS9OhoskvFLPwLoxoMtnw8o5tW4UOw@mail.gmail.com>
On Wed, Jan 10, 2024 at 11:43:50PM -0700, David G. Johnston wrote:
> On Wednesday, January 10, 2024, P C <puravc@gmail.com> wrote:
>
> Hello,
>
> While discussing with the development team, an interesting question came
> up: in a synchronous streaming replication scenario, with
> synchronous_commit = remote_apply, will the change be first visible on
> Standby (replica)? Primary will wait till the change is applied and
> committed on standby, and hence logically this looks to be correct. But I
> couldn't find this mentioned explicitly anywhere and hence seeking comments
> from the community.
>
>
> IIUC, no, the standbys can never reflect a newer state than what would be seen
> on the primary. The state of a given transaction, including most importantly
> the locking surrounding it, exists first on the primary and then is replicated
> to the secondary. Either the locking will prevent a dirty read or the dirty
> read will see the unconfirmed but committed data on the primary. The locking
> will be removed on the primary strictly before the standbys.
Uh, my research contradicts that, see:
https://momjian.us/main/blogs/pgblog/2020.html#June_3_2020
1 Write commit record to the write-ahead log
2 Flush the write-ahead log to durable storage
3 Update the pg_xact (slide 57)
4 Transfer to replicas
-> 5 Mark the commit as visible to other sessions (ProcArrayEndTransaction() updates PGPROC)
6 Communicate commit to the client
While the transaction will be visible on the primary to the creating
transaction session, it might not be visible to other sessions before it
appears on the replica.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: bruce@momjian.us, david.g.johnston@gmail.com, puravc@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Synchronous Replication: Where is data visible first?
In-Reply-To: <ZaHArQHT6dRnDm4A@momjian.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox