dynamic result sets support in extended query protocol

I want to progress work on stored procedures returning multiple result 
sets.  Examples of how this could work on the SQL side have previously 
been shown [0].  We also have ongoing work to make psql show multiple 
result sets [1].  This appears to work fine in the simple query 
protocol.  But the extended query protocol doesn't support multiple 
result sets at the moment [2].  This would be desirable to be able to 
use parameter binding, and also since one of the higher-level goals 
would be to support the use case of stored procedures returning multiple 
result sets via JDBC.

[0]: 
https://www.postgresql.org/message-id/flat/4580ff7b-d610-eaeb-e06f-4d686896b93b%402ndquadrant.com
[1]: https://commitfest.postgresql.org/29/2096/
[2]: https://www.postgresql.org/message-id/9507.1534370765%40sss.pgh.pa.us

(Terminology: I'm calling this project "dynamic result sets", which 
includes several concepts: 1) multiple result sets, 2) those result sets 
can have different structures, 3) the structure of the result sets is 
decided at run time, not declared in the schema/procedure definition/etc.)

One possibility I rejected was to invent a third query protocol beside 
the simple and extended one.  This wouldn't really match with the 
requirements of JDBC and similar APIs because the APIs for sending 
queries don't indicate whether dynamic result sets are expected or 
required, you only indicate that later by how you process the result 
sets.  So we really need to use the existing ways of sending off the 
queries.  Also, avoiding a third query protocol is probably desirable in 
general to avoid extra code and APIs.

So here is my sketch on how this functionality could be woven into the 
extended query protocol.  I'll go through how the existing protocol 
exchange works and then point out the additions that I have in mind.

These additions could be enabled by a _pq_ startup parameter sent by the 
client.  Alternatively, it might also work without that because the 
client would just reject protocol messages it doesn't understand, but 
that's probably less desirable behavior.

So here is how it goes:

C: Parse
S: ParseComplete

At this point, the server would know whether the statement it has parsed 
can produce dynamic result sets.  For a stored procedure, this would be 
declared with the procedure definition, so when the CALL statement is 
parsed, this can be noticed.  I don't actually plan any other cases, but 
for the sake of discussion, perhaps some variant of EXPLAIN could also 
return multiple result sets, and that could also be detected from 
parsing the EXPLAIN invocation.

At this point a client would usually do

C: Describe (statement)
S: ParameterDescription
S: RowDescription

New would be that the server would now also respond with a new message, say,

S: DynamicResultInfo

that indicates that dynamic result sets will follow later.  The message 
would otherwise be empty.  (We could perhaps include the number of 
result sets, but this might not actually be useful, and perhaps it's 
better not to spent effort on counting things that don't need to be 
counted.)

(If we don't guard this by a _pq_ startup parameter from the client, an 
old client would now error out because of an unexpected protocol message.)

Now the normal bind and execute sequence follows:

C: Bind
S: BindComplete
(C: Describe (portal))
(S: RowDescription)
C: Execute
S: ... (DataRows)
S: CommandComplete

In the case of a CALL with output parameters, this "primary" result set 
contains one row with the output parameters (existing behavior).

Now, if the client has seen DynamicResultInfo earlier, it should now go 
into a new subsequence to get the remaining result sets, like this 
(naming obviously to be refined):

C: NextResult
S: NextResultReady
C: Describe (portal)
S: RowDescription
C: Execute
....
S: CommandComplete
C: NextResult
...
C: NextResult
S: NoNextResult
C: Sync
S: ReadyForQuery

I think this would all have to use the unnamed portal, but perhaps there 
could be other uses with named portals.  Some details to be worked out.

One could perhaps also do without the DynamicResultInfo message and just 
put extra information into the CommandComplete message indicating "there 
are more result sets after this one".

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

One thing that's missing in this sequence is a way to specify the 
desired output format (text/binary) for each result set.  This could be 
added to the NextResult message, but at that point the client doesn't 
yet know the number of columns in the result set, so we could only do it 
globally.  Then again, since the result sets are dynamic, it's less 
likely that a client would be coded to set per-column output codes. 
Then again, I would hate to bake such a restriction into the protocol, 
because some is going to try.  (I suspect what would be more useful in 
practice is to designate output formats per data type.)  So if we wanted 
to have this fully featured, it might have to look something like this:

C: NextResult
S: NextResultReady
C: Describe (dynamic) (new message subkind)
S: RowDescription
C: Bind (zero parameters, optionally format codes)
S: BindComplete
C: Describe (portal)
S: RowDescription
C: Execute
...

While this looks more complicated, client libraries could reuse existing 
code that starts processing with a Bind message and continues to 
CommandComplete, and then just loops back around.

The mapping of this to libpq in a simple case could look like this:

PQsendQueryParams(conn, "CALL ...", ...);
PQgetResult(...);  // gets output parameters
PQnextResult(...);  // new: sends NextResult+Bind
PQgetResult(...);  // and repeat

Again, it's not clear here how to declare the result column output 
formats.  Since libpq doesn't appear to expose the Bind message 
separately, I'm not sure what to do here.

In JDBC, the NextResult message would correspond to the 
Statement.getMoreResults() method.  It will need a bit of conceptual 
adjustment because the first result set sent on the protocol is actually 
the output parameters, which the JDBC API returns separately from a 
ResultSet, so the initial CallableStatement.execute() call will need to 
process the primary result set and then send NextResult and obtain the 
first dynamic result as the first ResultSet for its API, but that can be 
handled internally.

Thoughts so far?

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