During the discussion on dynamic result sets[0], it became apparent that
the current way binary results are requested in the extended query
protocol is too cumbersome for some practical uses, and keeping that
style around would also make the proposed protocol extensions very
complicated.
The premise here is that a client library has hard-coded knowledge on
how to deal with binary format for certain, but not all, data types.
(Most client libraries process everything in text, and some client
libraries process everything in binary. Neither of these extremes are
of concern here.) Such a client always has to request a result row
description (Describe statement) before sending a Bind message, in order
to be able to pick out the result columns in should request in binary.
The feedback was that this extra round trip is often not worth it in
terms of performance, and so it is not done and binary format is not
used when it could be.
The conceptual solution is to allow a client to register for a session
which types it wants to always get in binary, unless it says otherwise.
In the discussion in [0], I pondered a new protocol message for that,
but after further thought, a GUC setting would do just as well.
The attached patch implements this. For example, to get int2, int4,
int8 in binary by default, you could set
SET default_result_formats = '21=1,23=1,20=1';
This is a list of oid=format pairs.
I think this format satisfies the current requirements of the JDBC
driver. But the format could also be extended in the future to allow
type names to be listed or some other ways of identifying the types.
In order to be able to test this via libpq, I had to add a little hack.
Currently, PQexecParams() and similar functions can only pass exactly
one result format code, which per protocol is then applied to all result
columns. There is no support for sending zero result format codes to
make the session default apply. I enabled this by allowing -1 to be
passed as the format code. I'm not sure if we want to make this part of
the official API, but it would be useful to have something like this
somehow.
[0]:
https://www.postgresql.org/message-id/flat/6e747f98-835f-2e05-cde5-86ee444a7140%402ndquadrant.com
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services