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

On Mon, 20 Mar 2023 at 15:09, Jeff Davis <pgsql(at)j-davis(dot)com> wrote:

> On Mon, 2023-03-20 at 14:36 -0400, Dave Cramer wrote:
> > Thanks for the review. I'm curious what system you are running on as
> > I don't see any of these errors.
>
> Are asserts enabled?
>
> > Well I'm guessing psql doesn't know how to read date or timestamptz
> > in binary. This is not a failing of the code.
>
> It seems strange, and potentially dangerous, to send binary data to a
> client that's not expecting it. It feels too easy to cause confusion by
> changing the GUC mid-session.
>
> Also, it seems like DISCARD ALL is not resetting it, which I think is a
> bug.
>
Thanks yes, this is a bug

>
> >
> > This is an interesting question. If the type isn't visible then it's
> > not visible to the query so
>
> I don't think that's true -- the type could be in a different schema
> from the table.

Good point. This seems to be the very difficult part.

>

> > >
> > > 5. There's a theoretical invalidation problem. It might also be a
> > > practical problem in some testing setups with long-lived
> > > connections
> > > that are recreating user-defined types.
> > >
> >
> > UDT's seem to be a problem here which candidly have very little use
> > case for binary output.
>
> I mostly agree with that, but it also might not be hard to support
> UDTs. Is there a design problem here or is it "just a matter of code"?
>
> >
> > I didn't try to solve it as Tom was OK with using a GUC. Using a
> > startup GUC is interesting,
> > but how would that work with pools where we want to reset the
> > connection when we return it and then
> > set the binary format on borrow ? By using a GUC when a client
> > borrows a connection from a pool the client
> > can reconfigure the oids it wants formatted in binary.
>
> That's a good point. How common is it to share a connection pool
> between different clients (some of which might support a binary format,
> and others which don't)? And would the connection pool put connections
> with and without the property in different pools?
>

For JAVA pools it's probably OK, but for pools like pgbouncer we have no
control of who is going to get the connection next.

>
> >
> > I really hadn't considered supporting type names. I have asked Paul
> > Ramsey about PostGIS and he doesn't see PostGIS using this.
>
> One of the things I like about Postgres is that the features all work
> together, and that user-defined objects are generally as good as built-
> in ones. Sometimes there's a reason to make a special case (e.g. syntax
> support or something), but in this case it seems like we could support
> user-defined types just fine, right? It's also just more friendly and
> readable to use type names, especially if it's a GUC.
>
> Regards,
> Jeff Davis
>
>