Re: CommandStatus from insert returning when using a portal. - Mailing list pgsql-hackers

From chap@anastigmatix.net
Subject Re: CommandStatus from insert returning when using a portal.
Date
Msg-id 1764703d2793b09fa607faa551a89b46@anastigmatix.net
Whole thread Raw
In response to Re: CommandStatus from insert returning when using a portal.  ("David G. Johnston" <david.g.johnston@gmail.com>)
Responses Re: CommandStatus from insert returning when using a portal.
Re: CommandStatus from insert returning when using a portal.
List pgsql-hackers
On 2023-07-12 21:30, David G. Johnston wrote:
> Right, and executeUpdate is the wrong API method to use, in the 
> PostgreSQL
> world, when executing insert/update/delete with the non-SQL-standard
> returning clause. ... ISTM that you are trying to make user-error less
> painful.

In Dave's Java reproducer, no user-error has been made, because the user
supplied a plain INSERT with the RETURN_GENERATED_KEYS option, and the
RETURNING clause has been added by the JDBC driver. So the user expects
executeUpdate to be the right method, and return the row count, and
getGeneratedKeys() to then return the rows.

I've seen a possibly even more interesting result using pgjdbc-ng with
protocol.trace=true:

FetchSize=0
<P<D<S
> 1.>t.>T$>Z*
<B<E<S
> 2.>D.>D.>C.>Z*
executeUpdate result: 2
ids: 1 2

FetchSize=1
<B<E<H
> 2.>D.>s*
executeUpdate result: -1
ids: 3 <E<H
> D.>s*
4 <E<H
> C*
<C<S
> 3.>Z*

FetchSize=2
<B<E<H
> 2.>D.>D.>s*
executeUpdate result: -1
ids: 5 6 <E<H
> C*
<C<S
> 3.>Z*

FetchSize=3
<B<E<H
> 2.>D.>D.>C*
<C<S
> 3.>Z*
executeUpdate result: 2
ids: 7 8


Unless there's some interleaving of trace and stdout messages happening
here, I think pgjdbc-ng is not even collecting all the returned rows
in the suspended-cursor case before executeUpdate returns, but keeping
the cursor around for getGeneratedKeys() to use, so executeUpdate
returns -1 before even having seen the later command complete, and would
still do that even if the command complete message had the right count.

Regards,
-Chap



pgsql-hackers by date:

Previous
From: Laurenz Albe
Date:
Subject: Re: PG 16 draft release notes ready
Next
From: chap@anastigmatix.net
Date:
Subject: Re: CommandStatus from insert returning when using a portal.