Re: Proposal to provide the facility to set binary format output for specific OID's per session

On Mon, Jul 25, 2022 at 4:57 AM Dave Cramer <davecramer(at)gmail(dot)com> wrote:

>
> Dave Cramer
>
>
> On Sun, 24 Jul 2022 at 23:02, Kyotaro Horiguchi <horikyota(dot)ntt(at)gmail(dot)com>
> wrote:
>
>> At Fri, 22 Jul 2022 11:00:18 -0400, Dave Cramer <davecramer(at)gmail(dot)com>
>> wrote in
>> > As a proof of concept I provide the attached patch which implements the
>> > ability to specify which oids will be returned in binary format per
>> > session.
>> ...
>> > Both the JDBC driver and the go driver can exploit this change with no
>> > changes. I haven't confirmed if other drivers would work without
>> changes.
>>
>> I'm not sure about the needs of that, but binary exchange format is
>> not the one that can be turned on ignoring the peer's capability.
>
> I'm not sure what this means. The client is specifying which types it
> wants in binary format.
>
>> If
>> JDBC driver wants some types be sent in binary format, it seems to be
>> able to be specified in bind message.
>>
> To be clear it's not just the JDBC client; the original idea came from the
> author of go driver.
> And yes you can specify it in the bind message but you have to specify it
> in *every* bind message which pretty much negates any advantage you might
> get out of binary format due to the extra round trip.
>
> Regards,
> Dave
>
>>
>> regards.
>>
>> --
>> Kyotaro Horiguchi
>> NTT Open Source Software Center
>>
>
The advantage is to be able to use the binary format with only a single
network round trip in cases where prepared statements are not possible.
e.g. when using PgBouncer. Using the simple protocol with this patch lets
users of pgx (the Go driver mentioned above) and PgBouncer use the binary
format. The performance gains can be significant especially with types such
as timestamptz that are very slow to parse.

As far as only sending binary types that the client can understand, the
client driver would call `set format_binary` at the beginning of the
session.

Jack Christensen