we are moving from Oracle to PostgreSQL and we hit a serious problem.
When executing a query with a large result set your driver loads each record in memory and this is a blocking problem for us.
The work-around is to use server-side cursors (http://jdbc.postgresql.org/documentation/head/query.html) but in order to do this we have to change autocommit mode of the transaction and this will change transaction semantics of out code.
Many JDBC drivers provide some swap-to-disk function in oredr to deal with huge result sets
Something like “if we are loading in RAM more than X MBs than swap on disk”
or “if we are loading in RAM more than X rows than swap on disk” (more simple to implement)