Re: [HACKERS] make async slave to wait for lsn to be replayed

On Sun, Mar 24, 2024 at 4:39 AM Bharath Rupireddy
<bharath(dot)rupireddyforpostgres(at)gmail(dot)com> wrote:
> I share the same concern as yours and had proposed something upthread
> [1]. The idea is something like how each query takes a snapshot at the
> beginning of txn/query (depending on isolation level), the same way
> the standby can wait for the primary's current LSN as of the moment
> (at the time of taking snapshot). And, primary keeps sending its
> current LSN as part of regular WAL to standbys so that the standbys
> doesn't have to make connections to the primary to know its current
> LSN every time. Perhps, this may not even fully guarantee (considered
> to be achieving) the read-after-write consistency on standbys unless
> there's a way for the application to tell the wait LSN.

Oh, no. Please, check [1]. The idea is to wait for a particular
transaction to become visible. The one who made a change on primary
brings the lsn value from there to replica. For instance, an
application made a change on primary and then willing to run some
report on replica. And the report should be guaranteed to contain the
change just made. So, the application query the LSN from primary
after making a write transaction, then calls pg_wait_lsn() on
replicate before running the report.

This is quite simple server functionality, which could be used at
application-level, ORM-level or pooler-level. And it unlocks the way
forward for in-protocol implementation as proposed by Peter
Eisentraut.

Links.
1. https://www.postgresql.org/message-id/CAPpHfdtny81end69PzEdRsROKnsybsj%3DOs8DUM-6HeKGKnCuQQ%40mail.gmail.com

------
Regards,
Alexander Korotkov