Java out of memory using PostgreSQL

Question

There is another possibly related question on this but it didn't have answers and the asker didn't clarify any.

So when I'm going over a large result set in postgres, java seems to break.

The query I'm running is trying to get around 5.5M rows. Basically I'm trying to populate a Neo4J database from a Postgres db.

I won't bother posting the code unless someone asks as it's pretty generic I think. Here is the exception output:

Exception in thread "main" java.lang.OutOfMemoryError: Java heap space
    at java.lang.Class.getDeclaredFields0(Native Method)
    at java.lang.Class.privateGetDeclaredFields(Unknown Source)
    at java.lang.Class.getDeclaredField(Unknown Source)
    at java.util.concurrent.atomic.AtomicReferenceFieldUpdater$AtomicReferenceFieldUpdaterImpl.<init>(Unknown Source)
    at java.util.concurrent.atomic.AtomicReferenceFieldUpdater.newUpdater(Unknown Source)
    at java.sql.SQLException.<clinit>(Unknown Source)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1818)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:512)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:374)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:254)
    at sqlToGraph.SqlToGraph.main(SqlToGraph.java:52)

Line 52 is just the query, as so:

rs = st.executeQuery("select player_lo, player_hi, total_hands_lo, total_hands_hi, (pc_lo + pc_hi)/2 as avg_pc from pair");

So I guess rs is getting pretty big. I suppose I could just use LIMIT and OFFSET and run the program a few hundred times or have it reset rs in a loop or so, but I'd rather not have to.

Answer 1

1. Set the fetch size to something reasonable (st.setFetchSize(rowCount)). You can experiment with this.
2. Put everything in a transaction with autocommit off (con.setAutoCommit(false)).

See this explanation and the documentation.