Subject: Re: dynamic result sets support in extended query protocol
To: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>,
 pgsql-hackers <pgsql-hackers@postgresql.org>
References: <6e747f98-835f-2e05-cde5-86ee444a7140@2ndquadrant.com>
From: Andrew Dunstan <andrew@dunslane.net>
Autocrypt: addr=andrew@dunslane.net; prefer-encrypt=mutual; keydata=
 mQENBE7KWFkBCAClridxur2AIc7eW2AR7izbfp3EnNefie2HbLF0izW5Ik5UjX2HBXBx4syI
 gY6b0ugohXrr274+baoAlvSbq6cAoQuEVrk5IZFzt20b1Xkx65FwGSEj526yiKLocqkJceSq
 Xr9xcA5SGY+FZv441chh5SU92v4q6z+6LPpoHOh97ptAVXZYNTtU0LevyvD5lja0TzbvJm6C
 eFXitJfnm1pLEr0DGJCR/iUOl/N62Kh4855zZC7NHIjQHPOvV5Stz/l5ilDhvGVk+xkXFPys
 SjZoUr1rXhYLpiyi5sR0X9FHXT0KnGuz1F5ERO7ZTLSSQ6fJwPj6gOk9K+vvoKvoeql5ABEB
 AAG0JEFuZHJldyBEdW5zdGFuIDxhbmRyZXdAZHVuc2xhbmUubmV0PokBPgQTAQIAKAIbAwYL
 CQgHAwIGFQgCCQoLBBYCAwECHgECF4AFAl3WlJ0FCRLOoywACgkQmfp/y1n8O4GxaQgAmtHC
 VYmtz4XxLLOu0p3aTcYrRNeTiFA5NahXljvOTODLO923Jh2HT4/PNsAOJ3L4Q5SEYoCn9Qh9
 BQUScpUGZmzInrcgE6iAYMqDX+T2CwJciuPt8SkPlYuupwkBmtcKKGH70+dyH4VL3zrK24as
 QUwncv+/632AqNCiIKa0nRCrlq/2bJaeD3ULgxrcuRVNyAwnfHGXqDo+7q865XE8JhQXjkml
 gjB06xaztVrZ8GMCDskSentmgC7a0q5w7MFFWztb1u0rDHfb0jlDaD9yOYGobkAY/E/xuHho
 7sJLotAWWAaN3t6j5Px7j2OW4sCIYM+sxlHAhrE8yiKlyyBmBbkBDQROylhZAQgA0ZzjNBQs
 7GFuo7Z3IPnViGoI1Gab2gpZqp/7CZbi2kN79VVzECc2LN3gtYId/OlvlwcC0aQ4948x/NZV
 /qe90Xt3d6CVu1/n37Mbs7uWVXTFJKgWKJ5AGysa+oMiddhf/iZFFcy3KEzfnf+2LEL1FYuz
 Wih/JiYI89ddjuH1Ht6fcHwGhWtEVx3OUhj80U2EUZb/12QSIaOCdrZGTp7XmSWfhm8UFZSD
 ben3fD+mpdzFjr6T/xMST4LhH4zrDujY8sY6N1n6Q+JRzDkfznN//JknBBfOKxdo127BYHpV
 p2z1gL12PqQ9HVzhUZGoBpgv74sAQ6quW+bZO+pCplG2cQARAQABiQElBBgBAgAPAhsMBQJd
 1pS0BQkSzqNVAAoJEJn6f8tZ/DuBCLkH/2wduZGWhk0JZbBkab472Ib8kZDFLRZZrgZpmrOp
 JNY5+3b+A7QGBqDz2vV2Org8WBJm3Me8pCmpjP/seQ+taHWOlxLzeL+5hjW7IddRS2l0Safh
 u405sS0PRg1omQZ2EBKwQKrpqTD67tYliYAez9KlHqiToeovDskcNVzaHl1WIt9OTSquANru
 yr8tUygmN7kK38q7dhpYBXphKbOg8/kGY9UWo/XU4xCQKq6si729MtcoVZfrbe8KapEMcnp7
 kYIMdsfxRJT7jAkF8/KZmR1wDs0ab+aZZ4mJYEUgPKPNjGuOS7y3L1NklWxpFedL44VDNfP0
 S7bBOkp0eKPjq5g=
Message-ID: <f31f0f23-c5a9-0bdf-e11c-3d8f35ee4fbc@dunslane.net>
Date: Fri, 9 Oct 2020 13:32:48 -0400
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101
 Thunderbird/68.9.0
MIME-Version: 1.0
In-Reply-To: <6e747f98-835f-2e05-cde5-86ee444a7140@2ndquadrant.com>
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Content-Language: en-US
Precedence: bulk


On 10/8/20 3:46 AM, Peter Eisentraut wrote:
> I want to progress work on stored procedures returning multiple result
> sets.=C2=A0 Examples of how this could work on the SQL side have previo=
usly
> been shown [0].=C2=A0 We also have ongoing work to make psql show multi=
ple
> result sets [1].=C2=A0 This appears to work fine in the simple query
> protocol.=C2=A0 But the extended query protocol doesn't support multipl=
e
> result sets at the moment [2].=C2=A0 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-4d68=
6896b93b%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.=C2=A0 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.=C2=A0 So we really need to use the existing ways of sending off t=
he
> queries.=C2=A0 Also, avoiding a third query protocol is probably desira=
ble
> 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.=C2=A0 I'll go through how the existing protoco=
l
> 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.=C2=A0 Alternatively, it might also work without that becaus=
e
> 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.=C2=A0 For a stored procedure, t=
his
> would be declared with the procedure definition, so when the CALL
> statement is parsed, this can be noticed.=C2=A0 I don't actually plan a=
ny
> 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.=C2=A0 The
> message would otherwise be empty.=C2=A0 (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.=C2=A0 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.=C2=A0 This is perhap=
s
> 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.=C2=A0=

> 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.=C2=A0 This cou=
ld
> 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.=C2=A0 Then again, since the result sets are dynami=
c,
> 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.=C2=A0 (I suspect what would be =
more
> useful in practice is to designate output formats per data type.)=C2=A0=
 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(...);=C2=A0 // gets output parameters
> PQnextResult(...);=C2=A0 // new: sends NextResult+Bind
> PQgetResult(...);=C2=A0 // and repeat
>
> Again, it's not clear here how to declare the result column output
> formats.=C2=A0 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.=C2=A0 It will need a bit of conceptu=
al
> 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?
>


Exciting stuff. But I'm a bit concerned about the sequence of
resultsets. The JDBC docco for CallableStatement says:

    A CallableStatement can return one ResultSet object or multiple
    ResultSet objects. Multiple ResultSet objects are handled using
    operations inherited from Statement.

    For maximum portability, a call's ResultSet objects and update
    counts should be processed prior to getting the values of output
    parameters.

And this is more or less in line with the pattern that I've seen when
converting SPs from other systems - the OUT params are usually set at
the end with things like status flags and error messages.

If the OUT parameter resultset has to come first (which is how I read
your proposal - please correct me if I'm wrong) we'll have to stack up
all the resultsets until the SP returns, then send the OUT params, then
send the remaining resultsets. That seems ... suboptimal.=C2=A0 The
alternative would be to send the OUT params last. That might result in
the driver needing to do some lookahead and caching, but I don't think
it's unmanageable. Of course, your protocol would also need changing.


cheers


andrew


--=20
Andrew Dunstan
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company