Thread: 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)
On Mon, 28 Oct 2024 at 17:58, Heikki Linnakangas <hlinnaka@iki.fi> wrote: > > - 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. I imagine a libpq interface like this. lsn = PQcurrentLSN(primaryConn) PQsendWaitLSN(secondaryConn, lsn) PQsendQuery(secondaryConn, ...) One thing I'm wondering is if the current lsn could be a read-only GUC that is reported through ParameterStatus. Because a downside of making it part of ReadyForQuery is that you only get a ReadyForQuery at the end of a pipeline, while a pipeline can contain multiple commits if you use explicit BEGIN/COMMIT in your pipeline. It might be nice to be able to wait on those commits before you've received ReadyForQuery. On the other hand, that seems like a rather exotic usecase that maybe is not worth thinking about too much.
Hi Peter, On 10/28/24 12:58 PM, Heikki Linnakangas wrote: > 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. I agree it is a good idea to have a feature like this. However, I agree with Heikki that we should have a separate message type for this. There are a lot of protocol implementations outside of PostgreSQL/Core, and they would have to adjust based on the version number of Core itself if we add fields to existing message types. Maybe there should be an "Extension ('x') (F)" message that only has a fixed "header", and the rest of the fields are based on the "header" limited by the message length field - sort of free-form. The result is returned as a "DataRow ('D') (B)" list. Thanks for working on this ! Best regards, Jesper
Hi, On 10/29/24 12:03 PM, Jesper Pedersen wrote: > On 10/28/24 12:58 PM, Heikki Linnakangas wrote: >> 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. > > I agree it is a good idea to have a feature like this. > > However, I agree with Heikki that we should have a separate message type > for this. There are a lot of protocol implementations outside of > PostgreSQL/Core, and they would have to adjust based on the version > number of Core itself if we add fields to existing message types. > > Maybe there should be an "Extension ('x') (F)" message that only has a > fixed "header", and the rest of the fields are based on the "header" > limited by the message length field - sort of free-form. The result is > returned as a "DataRow ('D') (B)" list. > I understand that we need this to be "atomic" which is difficult with the above since we can already use "Query 'Q' (F)", but we need it at "ReadyForQuery 'Z' (B)" level. Having a new "ReadyForQuery v2 ('z') (B)" would require changes to all non-Core implementations as well... Likely a protocol v4 thing. Best regards, Jesper
On Mon, 28 Oct 2024 at 17:58, Heikki Linnakangas <hlinnaka@iki.fi> wrote: > > - 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. Big +1 to this. After thinking about it more, I think this would make a fancy pooler much easier to implement. Because then the pooler could simply send such a new WaitForLSN message whenever it wants to, e.g. before handing off the server connection to the client. Instead of having to intercept every Query/Execute message that the client is sending, and modify that in place before sending it on to the server. Writing the previous down made me realize that using a separate message would be nice for this usecase too. As opposed to including it in ReadyForQuery. Because if the fancy pooler wants to configure these LSNs transparently for a client that has not set the protocol parameter, it would need to strip the new LSN field from the ReadyForQuery message before forwarding it to the client. Stripping out a whole message is generally easier to do than modifying messages in place.