From: | Peter Eisentraut <peter(at)eisentraut(dot)org> |
---|---|
To: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | protocol-level wait-for-LSN |
Date: | 2024-10-28 15:51:44 |
Message-ID: | 8e6ba982-fd41-4779-8729-eb120df69ea0@eisentraut.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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?
Attachment | Content-Type | Size |
---|---|---|
v0-0001-wait_for_lsn-protocol-option.patch | text/plain | 9.6 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2024-10-28 16:06:46 | Re: Better error reporting from extension scripts (Was: Extend ALTER OPERATOR) |
Previous Message | Robert Haas | 2024-10-28 15:47:19 | Re: pgsql: Implement pg_wal_replay_wait() stored procedure |