Re: Cursors for PGJDBC queries

Rashmi V Bharadwaj schrieb am 01.08.2019 um 09:10:
> I am trying to set the fetch size for my ResultSet to avoid Out of
> Memory exception. I have created the Statement with
> ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY and
> ResultSet.HOLD_CURSORS_OVER_COMMIT and I've also disabled auto commit
> as mentioned in the link Getting results based on a cursor
> <https://jdbc.postgresql.org/documentation/head/query.html#query-with-cursor>.
> I am still getting Out of memory error. My SQL query is a simple
> SELECT statement to retrieve all the rows from a table. According to
> https://postgrespro.com/list/thread-id/2370772, the holdability must
> be CLOSE_CURSORS_AT_COMMIT. Could you please confirm this is a
> requirement?

To rule out the obvious: you did call Statement.setFetchSize() before calling executeQuery()?

Using

connection.setAutoCommit(false);
Statement stmt = connection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(100);
ResultSet rs = stmt.executeQuery("....");
while (rs.next()) {
...
}

works perfectly for me, even with really large results.