This does seem very worthwhile. Can someone please sketch out a mini-design and see if it makes sense to the pgjdbc core? I'd be willing to hack some code, but, I'd want the design to be pre-vetted.
Here's my first quick strawman:
1.) Get the TEMP directory (may be OS specific).
2.) After reading more than 100 rows, determine the average size per row and check the available
JVM memory.
3.) Flush the rows to disk once more than 65% of JVM memory is exhausted OR according to
a parameter that can be set.
--Luss
On Sat, Sep 20, 2014 at 9:41 PM, Dave Cramer <pg@fastcrypt.com> wrote:
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)
