Re: Request for comment on setting binary format output per session - Mailing list pgsql-hackers
| From | Dave Cramer |
|---|---|
| Subject | Re: Request for comment on setting binary format output per session |
| Date | |
| Msg-id | CADK3HHLajD+buNGKNOc-cy6N+6=iinncLkOhYx6BkGeOn=krEA@mail.gmail.com Whole thread Raw |
| In response to | Re: Request for comment on setting binary format output per session (Merlin Moncure <mmoncure@gmail.com>) |
| Responses |
Re: Request for comment on setting binary format output per session
(Merlin Moncure <mmoncure@gmail.com>)
Re: Request for comment on setting binary format output per session (Jeff Davis <pgsql@j-davis.com>) |
| List | pgsql-hackers |
On Tue, 21 Mar 2023 at 07:35, Merlin Moncure <mmoncure@gmail.com> wrote:
On Mon, Mar 20, 2023 at 7:11 PM Dave Cramer <davecramer@gmail.com> wrote:On Mon, 20 Mar 2023 at 19:10, Merlin Moncure <mmoncure@gmail.com> wrote:On Mon, Mar 13, 2023 at 3:33 PM Dave Cramer <davecramer@gmail.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'sYeah. 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) 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
pgsql-hackers by date: