Re: CommandStatus from insert returning when using a portal.

On Fri, 14 Jul 2023 at 16:32, <chap(at)anastigmatix(dot)net> wrote:

> On 2023-07-14 15:49, Dave Cramer wrote:
> > On Fri, 14 Jul 2023 at 15:40, <chap(at)anastigmatix(dot)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.
>

I'll leave the user to decide their own fate.

Dave

>
> Regards,
> -Chap
>