protocol-level wait-for-LSN

Peter Eisentraut <peter@eisentraut.org>

From: Peter Eisentraut <peter@eisentraut.org>
To: pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2024-10-28T15:51:44Z
Lists: pgsql-hackers

Attachments

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.

Additionally, I'm exploring whether this is an idea for a protocol 
extension that might be a bit more complex than, say, longer cancel 
keys, something we could have a discussion around protocol versioning 
around.

The patch adds a protocol extension called _pq_.wait_for_lsn as well as 
a libpq connection option wait_for_lsn to activate the same.  (Use e.g., 
psql -d 'wait_for_lsn=1'.)

With this protocol extension, two things are changed:

- The ReadyForQuery message sends back the current LSN.

- The Query message sends an LSN to wait for.  (This doesn't handle the 
extended query protocol yet.)

To make any real use of this, you'd need some middleware, like a hacked 
pgbouncer, that transparently redirects queries among primaries and 
standbys, which doesn't exist yet.  But if it did, I imagine it could be 
pretty useful.

There might be other ways to slice this.  Instead of using a 
hypothetical middleware, the application would use two connections, one 
for writing, one for reading, and the LSN would be communicated between 
the two.  I imagine in this case, at least the one half of the protocol, 
shipping the current LSN with ReadyForQuery, could be useful, instead of 
requiring application code to issue pg_current_wal_insert_lsn() explicitly.

Thoughts?