Date: Fri, 12 Jan 2024 17:43:57 -0500
From: Bruce Momjian <bruce@momjian.us>
To: "David G. Johnston" <david.g.johnston@gmail.com>
Cc: P C <puravc@gmail.com>,
	"pgsql-admin@lists.postgresql.org" <pgsql-admin@lists.postgresql.org>
Subject: Re: Synchronous Replication: Where is data visible first?
Message-ID: <ZaHArQHT6dRnDm4A@momjian.us>
References: 
 <CADrzpjH8A_bOVU4vTwtDanrCuE5wt63NRcJ5QGQZZeLmaBuOgQ@mail.gmail.com>
 <CAKFQuwYUpW=LVcmjte1tqS9OhoskvFLPwLoxoMtnw8o5tW4UOw@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <CAKFQuwYUpW=LVcmjte1tqS9OhoskvFLPwLoxoMtnw8o5tW4UOw@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/ZaHArQHT6dRnDm4A%40momjian.us>
Precedence: bulk

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.