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

On Tue, 21 Mar 2023 at 07:35, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:

>
>
> On Mon, Mar 20, 2023 at 7:11 PM Dave Cramer <davecramer(at)gmail(dot)com> wrote:
>
>>
>>
>>
>> On Mon, 20 Mar 2023 at 19:10, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
>>
>>>
>>>
>>> On Mon, Mar 13, 2023 at 3:33 PM Dave Cramer <davecramer(at)gmail(dot)com>
>>> wrote:
>>>
>>>>
>>>> OIDs are a pain to deal with IMO. They will not survive a dump style
>>> restore, and are hard to keep synchronized between databases...type names
>>> don't have this problem. OIDs are an implementation artifact that ought
>>> not need any extra dependency.
>>>
>> AFAIK, OID's for built-in types don't change.
>> Clearly we need more thought on how to deal with UDT's
>>
>
> Yeah. Not having a solution that handles arrays and composites though
> would feel pretty incomplete since they would be the one of the main
> beneficiaries from a performance standpoint.
>
I don't think arrays of built-in types are a problem; drivers already know
how to deal with these.

> I guess minimally you'd need to expose some mechanic to look up oids, but
> being able to specify "foo"."bar", in the GUC would be pretty nice (albeit
> a lot more work).
>

As Jeff mentioned there is a visibility problem if the search path is
changed. The simplest solution IMO is to look up the OID at the time the
format is requested and use the OID going forward to format the output as
binary. If the search path changes and a type with the same name is now
first in the search path then the data would be returned in text.

>

>
>> This seems like a protocol or even a driver issue rather than a GUC
>>> issue. Why does the server need to care what format the client might want
>>> to prefer on a query by query basis?
>>>
>>
>> Actually this isn't a query by query basis. The point of this is that the
>> client wants all the results for given OID's in binary.
>>
>
> Yep. Your rationale is starting to click. How would this interact with
> existing code bases?
>
Actually JDBC wasn't the first to ask for this. Default result formats
should be settable per session · postgresql-interfaces/enhancement-ideas ·
Discussion #5 (github.com)
<https://github.com/postgresql-interfaces/enhancement-ideas/discussions/5> I've
tested it with JDBC and it requires no code changes on our end. Jack tested
it and it required no code changes on his end either. He did some
performance tests and found "At 100 rows the text format takes 48% longer
than the binary format."
https://github.com/postgresql-interfaces/enhancement-ideas/discussions/5#discussioncomment-3188599

I get that JDBC is the main target, but how does this interact with libpq
> code that explicitly sets resultformat?
>
Honestly I have no idea how it would function with libpq. I presume if the
client did not request binary format then things would work as they do
today.

Dave

>
>