Re: CommandStatus from insert returning when using a portal.

From: Chapman Flack <chap(at)anastigmatix(dot)net>
To: Dave Cramer <davecramer(at)gmail(dot)com>
Cc: "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: CommandStatus from insert returning when using a portal.
Date: 2023-07-14 22:12:32
Message-ID: 94d2cae02eccba9b4712f08e9757001b@anastigmatix.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On 2023-07-14 17:31, Chapman Flack wrote:
> So when getGeneratedKeys was later added, a way of getting a ResultSet
> after an executeUpdate, did they consciously intend it to come under
> the jurisdiction of existing apidoc that concerned the fetch size of
> a ResultSet you wanted from executeQuery?
> ...
> Moreover, the apidoc does say the fetch size is "a hint", and also that
> it applies "when more rows are needed" from the ResultSet.
>
> So it's technically not a misbehavior to disregard the hint, and you're
> not even disregarding the hint if you fetch all the rows at once,
> because
> then more rows can't be needed. :)

... and just to complete the thought, the apidoc for executeUpdate
leaves
no wiggle room for what that method returns: for DML, it has to be the
row count.

So if the only way to get the accurate row count is to fetch all the
RETURN_GENERATED_KEYS rows at once, either to count them locally or
to find the count in the completion message that follows them, that
mandate seems stronger than any hint from setFetchSize.

If someone really does want to do a huge INSERT and get the generated
values back in increments, it might be clearer to write an explicit
INSERT RETURNING and issue it with executeQuery, where everything will
work as expected.

I am also thinking someone might possibly allocate one Statement to
use for some number of executeQuery and executeUpdate calls, and might
call setFetchSize as a hint for the queries, but not expect it to have
effects spilling over to executeUpdate.

Regards,
-Chap

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Jacob Champion 2023-07-14 22:20:39 Re: pg_dump needs SELECT privileges on irrelevant extension table
Previous Message David G. Johnston 2023-07-14 21:33:14 Re: CommandStatus from insert returning when using a portal.