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?
The fly in the ointment here is when they setFetchSize and we decide to use a Portal under the covers.
I"m willing to document around this since it looks pretty unlikely that changing the behaviour in the server is a non-starter.
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.