Re: Cursors for PGJDBC queries

[Thomas Kellerer <spam_eater@xxxxxxx>]

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.