Re: protocol-level wait-for-LSN

On 28/10/2024 17:51, Peter Eisentraut 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.
> 
> 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.

+1

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

I'd suggest adding a new message type for this, so that it works the 
same with simple and extended query. Or if you just want to wait without 
issuing any query.

-- 
Heikki Linnakangas
Neon (https://neon.tech)