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
- v0-0001-wait_for_lsn-protocol-option.patch (text/plain) patch v0-0001
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?