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

+Paul Ramsey

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

> On Mon, 2023-03-13 at 16:33 -0400, Dave Cramer wrote:
> > Attached is a preliminary patch that takes a list of OID's. I'd like
> > to know if this is going in the right direction.
>
>
Thanks for the review. I'm curious what system you are running on as I
don't see any of these errors.

> I found a few issues:
>
> 1. Some kind of memory error:
>
> SET format_binary='25,1082,1184';
> WARNING: problem in alloc set PortalContext: detected write past
> chunk end in block 0x55ba7b5f7610, chunk 0x55ba7b5f7a48
> ...
> SET
>
2. Easy to confuse psql:
>
> CREATE TABLE a(d date, t timestamptz);
> SET format_binary='25,1082,1184';
> SELECT * FROM a;
> d | t
> ---+---
> ! |
> (1 row)
>
> Well I'm guessing psql doesn't know how to read date or timestamptz in
binary. This is not a failing of the code.

> 3. Some style issues
> - use of "//" comments
> - findOid should return bool, not int
>
> Sure will fix see attached patch

> When you add support for user-defined types, that introduces a couple
> other issues:
>
> 4. The format_binary GUC would depend on the search_path GUC, which
> isn't great.
>
This is an interesting question. If the type isn't visible then it's not
visible to the query so

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

>
>
> We've had this problem with binary for a long time, and it seems
> desirable to solve it. But I'm not sure GUCs are the right way.
>
> How hard did you try to solve it in the protocol rather than with a
> GUC? I see that the startup message allows protocol extensions by
> prefixing a parameter name with "_pq_". Are protocol extensions
> documented somewhere and would that be a reasonable thing to do here?
>

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.

>
> Also, if we're going to make the binary format more practical to use,
> can we document the expectations better?

Yes we can do that.

> It seems the expecatation is
> that the binary format just never changes, and that if it does, that's
> a new type name.
>
> I really hadn't considered supporting type names. I have asked Paul
Ramsey about PostGIS and he doesn't see PostGIS using this.

> Regards,
> Jeff Davis
>
>