Cursors for PGJDBC queries
<font face="Verdana,Arial,Helvetica,sans-serif" size="2"><div>Hi,</div><div><br></div><div>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 <a target="_blank" href="https://jdbc.postgresql.org/documentation/head/query.html#query-with-cursor" title="https://jdbc.postgresql.org/documentation/head/query.html#query-with-cursor">Getting results based on a cursor</a>. 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 <a target="_blank" href="https://postgrespro.com/list/thread-id/2370772" title="https://postgrespro.com/list/thread-id/2370772">https://postgrespro.com/list/thread-id/2370772</a>, the holdability must be CLOSE_CURSORS_AT_COMMIT. Could you please confirm this is a requirement?<br></div><div><br></div><div>Thanks,</div><div>Rashmi<br></div></font><BR>
Import Notes
Reply to msg id not found:
On Thu, Aug 1, 2019 at 9:10 AM Rashmi V Bharadwaj <rvbharad@in.ibm.com> wrote:
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. 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?
Hard to say without more information. Could it be something you need
to set on the jvm like
However, you should post on the JDBC mailing list
<https://www.postgresql.org/list/pgsql-jdbc/>.
On Thu, Aug 1, 2019 at 9:30 AM Luca Ferrari <fluca1978@gmail.com> wrote:
Hard to say without more information. Could it be something you need
to set on the jvm like
sorry, I was intended to write
-Xms256m
-Xmx1024m
to adjust the jvm memory limits.
Again I believe that the jdbc mailing list is the right place to ask
for such a problem.
Luca
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.