Re: CommandStatus from insert returning when using a portal.

[Dave Cramer]

David,

I will try to get a tcpdump file. Doing this in libpq seems challenging as
I'm not aware of how to create a portal in psql.

Chap

The only difference is one instance uses a portal to fetch the results, the
other (correct one) is a normal insert where all of the rows are returned
immediately

this is a reproducer in Java

conn.prepareStatement("DROP TABLE IF EXISTS test_table").execute();
conn.prepareStatement("CREATE TABLE IF NOT EXISTS test_table (id
SERIAL PRIMARY KEY, cnt INT NOT NULL)").execute();

for (var fetchSize : List.of(0, 1, 2, 3)) {
    System.out.println("FetchSize=" + fetchSize);

    try (var stmt = conn.prepareStatement("INSERT INTO test_table
(cnt) VALUES (1), (2) RETURNING id", RETURN_GENERATED_KEYS)) {
        stmt.setFetchSize(fetchSize);

        var ret = stmt.executeUpdate();
        System.out.println("executeUpdate result: " + ret);

        var rs = stmt.getGeneratedKeys();
        System.out.print("ids: ");
        while (rs.next()) {
            System.out.print(rs.getInt(1) + " ");
        }
        System.out.print("\n\n");
    }
}

Dave

On Fri, 14 Jul 2023 at 12:07, <c...@anastigmatix.net> wrote:

> On 2023-07-12 20:57, Dave Cramer wrote:
> > Without a cursor it returns right away as all of the results are
> > returned
> > by the server. However with cursor you have to wait until you fetch the
> > rows before you can get the CommandComplete message which btw is wrong
> > as
> > it returns INSERT 0 0 instead of INSERT 2 0
>
> To make sure I am following, was this describing a comparison of
> two different ways in Java, using JDBC, to perform the same operation,
> one of which behaves as desired while the other doesn't? If so, for
> my curiosity, what do both ways look like in Java?
>
> Or was it a comparison of two different operations, say one
> an INSERT RETURNING and the other something else?
>
> Regards,
> -Chap
>