Re: CommandStatus from insert returning when using a portal.

On 2023-07-14 15:49, Dave Cramer wrote:
> On Fri, 14 Jul 2023 at 15:40, <chap@anastigmatix.net> wrote:
>> Perhaps an easy rule would be, if the driver itself adds RETURNING
>> because of a RETURN_GENERATED_KEYS option, it should also force the
>> fetch count to zero and collect all the returned rows before
>> executeUpdate returns, and then it will have the right count
>> to return?
>
> Well that kind of negates the whole point of using a cursor in the case
> where you have a large result set.
>
> The rows are subsequently fetched in rs.next()

I guess it comes down, again, to the question of what kind of thing
the API client thinks it is doing, when it issues an INSERT with
the RETURN_GENERATED_KEYS option.

An API client issuing a plain INSERT knows it is the kind of thing
for which executeUpdate() is appropriate, and the complete success
or failure outcome is known when that returns.

An API client issuing its own explicit INSERT RETURNING knows it
is the kind of thing for which executeQuery() is appropriate, and
that some processing (and possibly ereporting) may be left to
occur while working through the ResultSet.

But now how about this odd hybrid, where the API client wrote
a plain INSERT, but added the RETURN_GENERATED_KEYS option?
The rewritten query is the kind of thing the server and the
driver need to treat as a query, but to the API client it still
appears the kind of thing for which executeUpdate() is suited.
The generated keys can then be examined--in the form of a
ResultSet--but one obtained with the special method
getGeneratedKeys(), rather than the usual way of getting the
ResultSet from a query.

Should the client then assume the semantics of executeUpdate
have changed for this case, the outcome isn't fully known yet,
and errors could come to light while examining the returned
keys? Or should it still assume that executeUpdate has its
usual meaning, all the work is done by that point, and the
ResultSet from getGeneratedKeys() is simply a convenient,
familiar interface for examining what came back?

I'm not sure if there's a firm answer to that one way or the
other in the formal JDBC spec, but the latter seems perhaps
safer to me.

Regards,
-Chap