I had a strange problem today when building an offline index of a large Postgres ResultSet. I launched jconsole and just watched the memory disappear until the heap was exhausted. After digging around it turns out that Postgres (unlike other JDBC drivers) will not use a cursor unless you set auto commit to false on your connection:
conn.setAutoCommit(false);
In addition I set specific options to the Statement:
Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
which also may have helped, but I didn't test without those settings. Now I can watch the memory oscillate in jconsole but it never gets above 50m and more importantly I can close my ticket...
