Subject: Re: dynamic result sets support in extended query protocol
To: Dave Cramer <davecramer@postgres.rocks>
References: <6e747f98-835f-2e05-cde5-86ee444a7140@2ndquadrant.com>
 <20201009184625.4hfgpcxzd6jfredb@alap3.anarazel.de>
 <CADK3HHKap0kzFKnHHEE94vb0ZL_sgdoJBVQT-PHWNt-kCkJAyQ@mail.gmail.com>
 <20201009185902.2hrednanwa7dxout@alap3.anarazel.de>
 <CADK3HHLgQ81rvsDqHr9XcS=mgR5hQXz1x4pv2vWKQOacaT8ETQ@mail.gmail.com>
 <26d034cc-d4af-e2f8-72b8-f23fea9320ed@2ndquadrant.com>
 <CADK3HH++n5iKy18i6cF5ZQKQLZQ3n8k9n42G8eWsxxBvp5RX7g@mail.gmail.com>
Cc: Andres Freund <andres@anarazel.de>,
 pgsql-hackers <pgsql-hackers@postgresql.org>,
 Vladimir Sitnikov <sitnikov.vladimir@gmail.com>,
 Shay Rojansky <roji@roji.org>, Mark Paluch <mpaluch@vmware.com>
From: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
Organization: 2ndQuadrant
Message-ID: <160f6d3e-466f-f75c-db41-e44355de29e8@2ndquadrant.com>
Date: Fri, 23 Oct 2020 10:59:38 +0200
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.14; rv:52.0)
 Gecko/20100101 Thunderbird/52.9.1
MIME-Version: 1.0
In-Reply-To: 
 <CADK3HH++n5iKy18i6cF5ZQKQLZQ3n8k9n42G8eWsxxBvp5RX7g@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 8bit
Precedence: bulk

On 2020-10-20 12:24, Dave Cramer wrote:
>     Finally, we could do it an a best-effort basis.  We use binary format
>     for registered types, until there is some invalidation event for the
>     type, at which point we revert to default/text format until the end
>     of a
>     session (or until another protocol message arrives re-registering the
>     type). 
> 
> Does the driver tell the server what registered types it wants in binary ?

Yes, the driver tells the server, "whenever you send these types, send 
them in binary" (all other types keep sending in text).

>     This should work, because the result row descriptor contains the
>     actual format type, and there is no guarantee that it's the same one
>     that was requested.
> 
>     So how about that last option?  I imagine a new protocol message, say,
>     TypeFormats, that contains a number of type/format pairs.  The message
>     would typically be sent right after the first ReadyForQuery, gets no
>     response. 
> 
> This seems a bit hard to control. How long do you wait for no response?

In this design, you don't need a response.

>     It could also be sent at any other time, but I expect that to
>     be less used in practice.  Binary format is used for registered
>     types if
>     they have binary format support functions, otherwise text continues to
>     be used.  There is no error response for types without binary support.
>     (There should probably be an error response for registering a type that
>     does not exist.)
> 
> I'm not sure we (pgjdbc) want all types with binary support functions 
> sent automatically. Turns out that decoding binary is sometimes slower 
> than decoding the text and the on wire overhead isn't significant. 
> Timestamps/dates with timezone are also interesting as the binary output 
> does not include the timezone.

In this design, you pick the types you want.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services