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

On Tue, Mar 21, 2023 at 8:22 AM Dave Cramer <davecramer(at)gmail(dot)com> wrote:

>
> 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.
>

Only if the name is not fully qualified. By allowing OID to bypass
visibility, it stands to reason visibility ought to be bypassed for type
requests as well, or at least be able to be. If we are setting things in
GUC, that suggests we can establish things in postgresql.conf, and oids
feel out of place there.

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
>

Yeah, the general need is very clear IMO.

> 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.
>

I see your argument here, but IMO this is another can of nudge away from
GUC, unless you're willing to establish that behavior. Thinking here is
that the GUC wouldn't do anything for libpq, uses cases, and couldn't,
since resultformat would be overriding the behavior in all interesting
cases...it seems odd to implement server side specified behavior that the
client library doesn't implement.

merlin