Обсуждение: Cursor problems

I was having trouble doing checkpoint commits on a cursor.  It's
possible I'm misunderstanding something basic, but it seems like
commiting on the same connection a cursor is on breaks the cursor.

Here's a pared-down snippet of code giving me the problem:

conn.setAutoCommit(false);

             pstmt = conn.prepareStatement("SELECT crash_id FROM
crash.crash_logs");
             pstmt.setFetchSize(50);

             rs = pstmt.executeQuery();

             pstmt = conn.prepareStatement("insert into blah " +
                     "values (?)");

             while(rs.next()) {
                 pstmt.setInt(1, rs.getInt("crash_id"));

                 pstmt.executeUpdate();

                 if(rs.getRow() % 50 == 0) {
                     System.out.println("Committing " + rs.getRow());
                     conn.commit();
                 }
             }

             conn.commit();

When I run it, I get the following error after the commit:
ERROR: portal "C_3" does not exist

I solved it by using two separate connections, but I'm not sure if
this is a bug or a misunderstanding on my part.

Jeff

Jeffrey Melloy wrote:
> I was having trouble doing checkpoint commits on a cursor.  It's
> possible I'm misunderstanding something basic, but it seems like
> commiting on the same connection a cursor is on breaks the cursor.

[...]

> When I run it, I get the following error after the commit:
> ERROR: portal "C_3" does not exist

ResultSets aren't guaranteed to stay valid across a commit unless you
specify ResultSet.HOLD_CURSORS_OVER_COMMIT when creating the statement
(the default is ResultSet.CLOSE_CURSORS_AT_COMMIT). In theory we should
actually close the ResultSet in your case (and you'd get a "resultset is
closed" exception the next time you used it), but currently we don't do
that so instead you see that "portal does not exist" error the next time
the ResultSet needs to refill itself from the server.

Unfortunately the driver doesn't support HOLD_CURSORS_OVER_COMMIT yet.
One simple implementation is just to disable use of cursors internally
when HOLD_CURSORS is specified, but that somewhat defeats the purpose..
To do it properly either needs a protocol change so that
protocol-created portals can be created WITH HOLD, or we'd need to start
using explicit DECLARE CURSOR statements rather than protocol-level portals.

-O