Re: Request for comment on setting binary format output per session

On Wed, Mar 29, 2023 at 12:04 PM Jeff Davis <pgsql@j-davis.com> wrote:
> I'm starting to agree that the awkwardness around connection poolers is
> a problem. If that's the case, I'm wondering if the protocol extensions
> will ever be useful.

In the case at hand, it seems like the problem could easily be solved
by allowing the property to be changed after connection startup.
Instead of using the protocol extension mechanism to negotiate a
specific value for the property, we can use it to negotiate about
whether or not some new protocol message that can be used to change
that property is supported. If it is, then a new value can be set
whenever, and a connection pooler can switch the active value when it
associates the server's session with a different client session.

Alternatively, the protocol extension mechanism can be used to
negotiate an initial value for the property, with the understanding
that if any initial value is negotiated, that also implies that the
server will accept some new protocol message later in the session to
change the value. If no initial value is negotiated, the client can't
assume that the server even knows about that property and can't try to
change it.

Backing up a level, the purpose of the protocol extension mechanism is
to help us agree on the communication protocol -- that is, the set of
messages that we can send and receive on a certain connection. The
question for the protocol extension mechanism isn't "which types
should always be sent in binary format?" but "would it be ok if I
wanted you to always send certain types in binary format?", with the
idea that if the answer is yes it will still be necessary for the
client to let the server know which ones, but that's easy to do if
we've agreed on the concept that it's OK for me to ask the server for
that. And if it's OK for me to ask that once, it should also be OK for
me to later ask for something different.

This could, perhaps, be made even more general yet. We could define a
concept of "protocol session parameters" and make "which types are
always sent in binary format?" one of those parameters. So then the
conversation could go like this:

C: Hello! Do you know about protocol session parameters?
S: Why yes, actually I do.
C: Cool. I would like to set the protocol session parameter
types_always_in_binary_format=timestamptz. Does that work for you?
S: Sure thing! (or alternatively: Sadly, I've not heard of that
particular protocol session parameter, sorry to disappoint.)

The reason why I suggest this is that I feel like there could be a
bunch of things like this. The set of things to be sent in binary
format feels like a property of the wire protocol, not something
SQL-level that should be configured via SET.  Clients, drivers, and
connection poolers aren't going to want to have to worry about some
user screwing up the session by changing that property inside of a
function or procedure or whatever. But there could also be a bunch of
different things like this that we want to support. For example, one
that would be really useful for connection poolers is the session
user. The pooler would like to change the session user whenever the
connection is changed to talk to a different client, and it would like
that to happen in a way that can't be reversed by issuing any SQL
command. I expect in time we may find a bunch of others.

--
Robert Haas
EDB: http://www.enterprisedb.com