Re: dynamic result sets support in extended query protocol

Regarding decoding binary vs text performance: There can be a significant
performance cost to fetching the binary format over the text format for
types such as text. See
https://www.postgresql.org/message-id/CAMovtNoHFod2jMAKQjjxv209PCTJx5Kc66anwWvX0mEiaXwgmA%40mail.gmail.com
for the previous discussion.

From the pgx driver (https://github.com/jackc/pgx) perspective:

A "use binary for these types" message sent once at the beginning of the
session would not only be helpful for dynamic result sets but could
simplify use of the extended protocol in general.

Upthread someone posted a page pgjdbc detailing desired changes to the
backend protocol (
https://github.com/pgjdbc/pgjdbc/blob/master/backend_protocol_v4_wanted_features.md)
I concur with almost everything there, but in particular the first
suggestion of the backend automatically converting binary values like it
does text values would be huge. That combined with the "use binary for
these types" message could greatly simplify the driver side work in using
the binary format.

CommandComplete vs ReadyForQuery -- pgx does the same as Npgsql in that it
bundles batches multiple queries together in the extended protocol and uses
CommandComplete for statement completion and ReadyForQuery for batch
completion.

On Tue, Oct 20, 2020 at 9:28 AM Shay Rojansky <roji(at)roji(dot)org> wrote:

> Very interesting conversation, thanks for including me Dave. Here are some
> thoughts from the Npgsql perspective,
>
> Re the binary vs. text discussion... A long time ago, Npgsql became a
> "binary-only" driver, meaning that it never sends or receives values in
> text encoding, and practically always uses the extended protocol. This was
> because in most (all?) cases, encoding/decoding binary is more efficient,
> and maintaining two encoders/decoders (one for text, one for binary) made
> less and less sense. So by default, Npgsql just requests "all binary" in
> all Bind messages it sends (there's an API for the user to request text, in
> which case they get pure strings which they're responsible for parsing).
> Binary handling is implemented for almost all PG types which support it,
> and I've hardly seen any complaints about this for the last few years. I'd
> be interested in any arguments against this decision (Dave, when have you
> seen that decoding binary is slower than decoding text?).
>
> Given the above, allowing the client to specify in advance which types
> should be in binary sounds good, but wouldn't help Npgsql much (since by
> default it already requests binary for everything). It would slightly help
> in allowing binary-unsupported types to automatically come back as text
> without manual user API calls, but as I wrote above this is an extremely
> rare scenario that people don't care much about.
>
> > Is there really a good reason for forcing the client to issue
> NextResult, Describe, Execute for each of the dynamic result sets?
>
> I very much agree - it should be possible to execute a procedure and
> consume all results in a single roundtrip, otherwise this is quite a perf
> killer.
>
> Peter, from your original message:
>
> > Following the model from the simple query protocol, CommandComplete
> really means one result set complete, not the whole top-level command.
> ReadyForQuery means the whole command is complete. This is perhaps
> debatable, and interesting questions could also arise when considering what
> should happen in the simple query protocol when a query string consists of
> multiple commands each returning multiple result sets. But it doesn't
> really seem sensible to cater to that
>
> Npgsql implements batching of multiple statements via the extended
> protocol in a similar way. In other words, the .NET API allows users to
> pack multiple SQL statements and execute them in one roundtrip, and Npgsql
> does this by sending
> Parse1/Bind1/Describe1/Execute1/Parse2/Bind2/Describe2/Execute2/Sync. So
> CommandComplete signals completion of a single statement in the batch,
> whereas ReadyForQuery signals completion of the entire batch. This means
> that the "interesting questions" mentioned above are possibly relevant to
> the extended protocol as well.
>