Re: protocol-level wait-for-LSN

On Mon, 28 Oct 2024, 16:51 Peter Eisentraut, <peter(at)eisentraut(dot)org> wrote:
>
> This is something I hacked together on the way back from pgconf.eu.
> It's highly experimental.
>
> The idea is to do the equivalent of pg_wal_replay_wait() on the protocol
> level, so that it is ideally fully transparent to the application code.
> The application just issues queries, and they might be serviced by a
> primary or a standby, but there is always a correct ordering of reads
> after writes.

+1

> Thoughts?

I think it would be quite beneficial to include the cluster ID in
these messages, so that e.g. logical replication can be guaranteed to
be cought up to the recent commit when querying a sharded cluster.

So instead of just LSN, PostgreSQL would return the [cluster_id, LSN]
pair (maybe: pairs, given that you may want to have forward-only view
of a logical primary across 2 different logical subscribers; postgres
could supply a pair [cluster_id, LSN] for every logical subscriber
slot), while the "wait for LSN" protocol feature would accept a list
of these, waiting for the logical subscriptions and/or physical
replication that source their changes from those cluster_ids to catch
up to those LSNs.

Kind regards,

Matthias van de Meent
Neon (https://neon.tech)