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

On Fri, Mar 22, 2024 at 12:58 AM Peter Eisentraut <peter(at)eisentraut(dot)org> wrote:
> On 17.03.24 15:09, Alexander Korotkov wrote:
> > My current attempt was to commit minimal implementation as less
> > invasive as possible. A new clause for BEGIN doesn't require
> > additional keywords and doesn't introduce additional statements. But
> > yes, this is still a new qual. And, yes, Amit you're right that even
> > if I had committed that, there was still a high risk of further
> > debates and revert.
>
> I had written in [0] about my questions related to using this with
> connection poolers. I don't think this was addressed at all. I haven't
> seen any discussion about how to make this kind of facility usable in a
> full system. You have to manually query and send LSNs; that seems
> pretty cumbersome. Sure, this is part of something that could be
> useful, but how would an actual user with actual application code get to
> use this?

The current usage pattern of this functionality is the following.

1. Do the write transaction on primary
2. Query pg_current_wal_insert_lsn() on primary
3. Call pg_wait_lsn() with the value obtained on the previous step on replica
4. Do the read transaction of replica

This usage pattern could be implemented either on the application
level, or on the pooler level. For application level, it would
require a somewhat advanced level of database-aware programming, but
this is still a valid usage. Regarding poolers, if some poolers
manage to automatically distinguish reading and writing queries,
dealing with LSNs wouldn't be too complex for them.

Having this functionality on protocol level would be ideal, but let's
do this step-by-step. The built-in procedure isn't very invasive, but
that could give us some adoption and open the way forward.

------
Regards,
Alexander Korotkov