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.