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

On Wed, 4 Oct 2023 at 21:10, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
> On Wed, Oct 4, 2023 at 10:17 AM Peter Eisentraut <peter(at)eisentraut(dot)org> wrote:
> > I think intuitively, this facility ought to work like client_encoding.
>
> I hadn't really considered client_encoding as a precedent for this
> setting. A lot of my discomfort with the proposed mechanism also
> applies to client_encoding, namely, suppose you call some function or
> procedure or whatever and it changes client_encoding on your behalf
> and now your communication with the server is all screwed up. That
> seems very unpleasant. Yet it's also existing behavior. I think one
> could conclude on these facts either that (a) client_encoding is fine
> and the problems with controlling behavior using that kind of
> mechanism are mostly theoretical or (b) that we messed up with
> client_encoding and shouldn't add any more mistakes of the same ilk or
> (c) that we should really be looking at redesigning the way
> client_encoding works, too.

With my PgBouncer maintainer hat on: I think the GUC approach would be
quite alright, i.e. option (a). The nice thing is that it would be
very simple to make it work with connection poolers, because the same
approach could be reused that is currently used for client_encoding.
NOTE: This does require that the new GUC has the GUC_REPORT flag set
(just like client_encoding). By adding the GUC_REPORT flag clients
could also take into account any changes to the setting even when they
did not change it themselves (simplest way to handle a change would be
by throwing an error and closing the connection).

To clarify how PgBouncer currently handles client_encoding: For each
client PgBouncer keeps track of the current value for a list of GUCs,
one of which is client_encoding. This is done by listening for the
ParameterStatus responses it gets from the server while the client is
connected. Then if later a client is assigned another server
connection, and that server has different values for (some of) these
GUCs, before actually forwarding the client its query some SET
commands are sent to correctly set the GUCs.

The resultFormat = 3 trick might be nice for backwards compatibility
of clients. That way old clients would continue to get text or binary
output even when the new GUC is set. To be clear resultFormat=3 would
mean: Use binary format when the new GUC indicates that it should.
Upthread I see that Dave mentioned that this would require an extra
Describe, but I don't understand why. If you set 3 for all columns and
you know the value of the GUC, then you know which columns will be
encoded in binary.