jdbc + large postgresql query give out of memory

Question

I'm trying to execute a postgresql query which returns a large result:

connection.setAutoCommit(false);
st = connection.createStatement(
  ResultSet.CONCUR_READ_ONLY,
  ResultSet.TYPE_FORWARD_ONLY
);
st.setFetchSize(100);
logMemory();
System.out.println("start query ");
rs = st.executeQuery(queryString);
System.out.println("done query ");
logMemory();

but this uses a lot of memory:

Free memory; 4094347680  (= 3905 mb).
start query 
done query
Free memory; 2051038576  (= 1956 mb).

(printed with Runtime.getRuntime().freeMemory() )

So far it works but the database is going to be a lot bigger. I don't ever need the entire result in memory; I just need to proccess each row, write the results to disk and go to the next row.

I know 'setFetchSize' is only a hint, but I would find it strange if postgresql/jdbc would ignore it, as it's around for ages.

Any way to get around this? My only idea so far is to make a batch script which streams the result of the query to disk and then parse the file from Java...

Answer 1

Here are the guidelines for ensuring that the result set is actually retrieved with a cursor. You seem to hit on all of the known ones in your code, but you haven't specified the statement, so it may be several strung together with semicolons (unlikely, by the looks of your code). You have to be using the V3 protocol (version 7.4 or later). Do all of these things apply to your case?

Answer 2

Ouch, this is one of the most nasty bugs using JDBC I've seen. You should change

st = connection.createStatement(
  ResultSet.CONCUR_READ_ONLY,
  ResultSet.TYPE_FORWARD_ONLY
);

into

st = connection.createStatement(
  ResultSet.TYPE_FORWARD_ONLY,
  ResultSet.CONCUR_READ_ONLY
);

Maybe simply

st = connection.createStatement();

will work as well (as you've met the other criteria for a cursor).