Re: dynamic result sets support in extended query protocol

On 2020-10-09 20:46, Andres Freund wrote:
> Is there really a good reason for forcing the client to issue
> NextResult, Describe, Execute for each of the dynamic result sets? It's
> not like there's really a case for allowing the clients to skip them,
> right? Why aren't we sending something more like
>
> S: CommandPartiallyComplete
> S: RowDescription
> S: DataRow...
> S: CommandPartiallyComplete
> S: RowDescription
> S: DataRow...
> ...
> S: CommandComplete
> C: Sync

I want to post my current patch, to keep this discussion moving. There
are still a number of pieces to pull together, but what I have is a
self-contained functioning prototype.

The interesting thing about the above message sequence is that the
"CommandPartiallyComplete" isn't actually necessary. Since an Execute
message normally does not issue a RowDescription response, the
appearance of one is already enough to mark the beginning of a new
result set. Moreover, libpq already handles this correctly, so we
wouldn't need to change it at all.

We might still want to add a new protocol message, for clarity perhaps,
and that would probably only be a few lines of code on either side, but
that would only serve for additional error checking and wouldn't
actually be needed to identify what's going on.

What else we need:

- Think about what should happen if the Execute message specifies a row
count, and what should happen during subsequent Execute messages on the
same portal. I suspect that there isn't a particularly elegant answer,
but we need to pick some behavior.

- Some way for psql to display multiple result sets. Proposals have been
made in [0] and [1]. (You need either patch or one like it for the
regression tests in this patch to pass.)

- Session-level default result formats setting, proposed in [2]. Not
strictly necessary, but would be most sensible to coordinate these two.

- We don't have a way to test the extended query protocol. I have
attached my test program, but we might want to think about something
more permanent. Proposals for this have already been made in [3].

- Right now, this only supports returning dynamic result sets from a
top-level CALL. Specifications for passing dynamic result sets from one
procedure to a calling procedure exist in the SQL standard and could be
added later.

(All the SQL additions in this patch are per SQL standard. DB2 appears
to be the closest existing implementation.)

[0]:
https://www.postgresql.org/message-id/flat/4580ff7b-d610-eaeb-e06f-4d686896b93b%402ndquadrant.com
[1]: https://commitfest.postgresql.org/29/2096/
[2]: https://commitfest.postgresql.org/31/2812/
[3]:
https://www.postgresql.org/message-id/4f733cca-5e07-e167-8b38-05b5c9066d04%402ndQuadrant.com

--
Peter Eisentraut
2ndQuadrant, an EDB company
https://www.2ndquadrant.com/