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(at)eisentraut(dot)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