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

Thank you for your interest to the patch.
I understand you questions, but I fully support Alexander Korotkov idea
to commit the minimal required functionality. And then keep working on
other improvements.

On 2024-03-24 05:39, Bharath Rupireddy wrote:
> On Fri, Mar 22, 2024 at 4:28 AM Peter Eisentraut <peter@eisentraut.org> 
> wrote:
>> 
>> 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?
>> 
>> [0]:
>> https://www.postgresql.org/message-id/8b5b172f-0ae7-d644-8358-e2851dded43b%40enterprisedb.com


>>> But I wonder how a client is going to get the LSN.  How would all of
>>> this be used by a client?  I can think of a scenarios where you have
>>> an application that issues a bunch of SQL commands and you have some
>>> kind of pooler in the middle that redirects those commands to
>>> different hosts, and what you really want is to have it transparently
>>> behave as if it's just a single host.  Do we want to inject a bunch
>>> of "SELECT pg_get_lsn()", "SELECT pg_wait_lsn()" calls into that?

As I understand your question, application make dml on the primary
server, get LSN of changes and send bunch SQL read-only commands to 
pooler. Transparent behave we can get using #synchronous_commit, but
it is very slow.

>>> I'm tempted to think this could be a protocol-layer facility.  Every
>>> query automatically returns the current LSN, and every query can also
>>> send along an LSN to wait for, and the client library would just keep
>>> track of the LSN for (what it thinks of as) the connection.  So you
>>> get some automatic serialization without having to modify your client 
>>> code.

Thank you, it is a good question for future versions.
You say about a protocol-layer facility, what you meen. May be we can
use signals, like hot_standby_feedback.

> 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.
> 
> Thoughts?
> 
> [1] 
> https://www.postgresql.org/message-id/CALj2ACUfS7LH1PaWmSZ5KwH4BpQxO9izeMw4qC3a1DAwi6nfbQ%40mail.gmail.com


> +1 to have support for implicit txns. A strawman solution I can think
> of is to let primary send its current insert LSN to the standby every
> time it sends a bunch of WAL, and the standby waits for that LSN to be
> replayed on it at the start of every implicit txn automatically.

And how standby will get lsn to wait for? All solutions I can think of
are very invasive and poorly scalable.

For example, every dml can send back LSN if dml is success. And 
application could use it to wait actual changes.

> The new BEGIN syntax requires application code changes. This led me to
> think how one can achieve read-after-write consistency today in a
> primary - standby set up. All the logic of this patch, that is, waiting
> for the standby to pass a given primary LSN needs to be done in the
> application code (or in proxy or in load balancer?). I believe there
> might be someone doing this already, it's good to hear from them.

You may use #synchronous_commit mode but it slow. So my implementation
don`t make primary to wait all standby to sent its feedbacks.

-- 
Ivan Kartyshov
Postgres Professional: www.postgrespro.com