Today, I had to query a table in PostgreSQL DB that had 1,84,692 records in it, using JDBC.
I got a Connection object, prepared a statement as “select * from foo” and got the result set. I tried to print the field I was intersted in. Bam, I got this error “Exception in thread “main” java.lang.OutOfMemoryError: Java heap space”. I was a bit baffled as I had taken care to set the fetch size of the prepared statement, so that all the results are not loaded into memory. I changed my eclipse heap size to 512 MB and gave it a shot again. Still the same error. Now I went to my very good friend google and started poking around. Rest of this post is a summary of what I learnt.
I was under the impression that setting the fetch size on a prepared statement would not load the whole result set into memory. A closer look at the API spec says something else. This is what the API spec says about setting the fetch size “Gives the JDBC driver a hint as to the number of rows that should be fetched from the database when more rows are needed”. The operative word here is “HINT”. So it means this behavior is not gauranteed.
For PostgreSQL not to load the whole resultset into memory, it should use cursors. Setting the fetch size of the prepared statement should have told PostgreSQL to use cursors. When a cursor is used, multiple queries are fired when more results are needed instead of loading all the results into memory in a single shot. But the catch is that, PostgreSQL can use cursors only in a transaction block. And I guess the JDBC driver of PostgreSQL executes statements inside a transaction block only if the connection object’s auto commit is set to false. As I had not set my connection object’s auto commit to false, my query was not being executed in a transaction block. As soon as I set my connection object’s auto commit to false, my memory error disappeared and things were back to normal.
Thanks a lot! I love people who take the time to share things like this on the net.
Thanks for the appreciation :)
Thanks, this about fetch size and auto commit mode is exactly the information I was looking for.
It would be simplier if the driver’s authors added some warning message about ignoring fetch size parameter.
You are welcome.
Thanks! This was a useful post, even three years later. :)
By the way, after a bit more research, I discovered that at least as of now (I have no idea about in 2009), this problem is mentioned in the Postgres JDBC driver docs – http://jdbc.postgresql.org/documentation/head/query.html#query-with-cursor
It is the best time to make some plans for the longer term and it’s time to be happy. I’ve learn this
post and if I may just I wish to recommend you some interesting things or tips.
Perhaps you could write subsequent articles referring to this article.
I desire to read even more issues approximately
it!
whoah this blog is great i love studying your posts. Stay up the good work!
You understand, lots of people are looking round for this information, you can
help them greatly.
Its like you learn my thoughts! You appear to understand a lot approximately this, like you wrote the guide in it or something.
I feel that you can do with some percent to power the message house a little
bit, however other than that, that is fantastic blog. An excellent
read. I’ll definitely be back.
Have you ever thought about including a little bit
more than just your articles? I mean, what you say is
fundamental and all. Nevertheless think about if you added some great images or videos to give your posts more, “pop”!
Your content is excellent but with images and videos, this website could definitely be one of the very best in its niche.
Terrific blog!
Hello everybody, here every person is sharing these experience, thus it’s nice to read this web site, and I used to go to see this website all the time.
Thanks to my father who told me concerning
this website, this webpage is really awesome.
Great post. I was checking continuously this blog and
I’m impressed! Extremely useful info specifically the
last part :) I care for such info a lot. I was looking for this
certain info for a long time. Thank you and best of luck.
I believe what you published made a lot of sense. However, what about this?
suppose you added a little content? I mean, I don’t
wish to tell you how to run your blog, but suppose you added a
post title that grabbed people’s attention? I mean PostgreSql, JDBC and large result sets | return this.contents() is a little boring.
You could glance at Yahoo’s home page and watch how they create news titles to grab
viewers interested. You might try adding a video or a picture or two to get people excited about
what you’ve written. In my opinion, it would bring your posts a little
bit more interesting.