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

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).