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

Just curious, what is the max heap size you are running with? Or are you using default? — matt b
– matt b, Commented Oct 1, 2009 at 15:45

Yishai · Accepted Answer · 2013-09-10 14:30:42Z

13

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?

edited Sep 10, 2013 at 14:30

answered Oct 1, 2009 at 14:55

Yishai

92.4k31 gold badges195 silver badges266 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

kresjer Over a year ago

Yeah, I've tried switching on/off all the guidelines. The statement is simply Select hh.data, hh.customer_ID from dataTable hh join customer PH on hh.customer_ID = PH.customer_ID ; and it's postgresql 8.3 and I'm using postgresql-8.3-603.jdbc4.jar .

Yishai Over a year ago

I'm stumped. I'd say the next best step is to post on groups that focus on Postgresql. There are probably some other non-obvious things that causes/can force the connection to use a cursor. I'd crack open the JDBC source code (that is the nice thing about open source) and see what is going on in your scenario.

jutky Over a year ago

Thanks a lot for the answer. I was struggling this problem for the whole day, until I've found a requirement for conn.setAutoCommit(false) on the page you cited.

Frank Meulenaar · Accepted Answer · 2009-10-01 20:17:28Z

11

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

answered Oct 1, 2009 at 20:17

Frank Meulenaar

1,2293 gold badges14 silver badges25 bronze badges

2 Comments

rogerdpack Over a year ago

what's the bug? is it a real memory leak or is something else going on? Do you think it's postgres only?

digital_infinity Over a year ago

@rogerdpack OP changed the method parameters for createStatement. Both parameters are 'int'-s but means some different things. So it is not any bug in the JDBC implementation.

Collectives™ on Stack Overflow

jdbc + large postgresql query give out of memory

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related