Re: Disk buffering of resultsets

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(at)fastcrypt(dot)com> wrote:

> Enrico,
>
> Patches are welcome. Please do !
>
> Dave Cramer
>
> dave.cramer(at)credativ(dot)ca
> http://www.credativ.ca
>
> On 19 September 2014 06:44, Enrico Olivelli - Diennea <
> enrico(dot)olivelli(at)diennea(dot)com> wrote:
>
>> Hi,
>>
>> 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)
>>
>>
>>
>> We can contribute with some patch
>>
>>
>>
>> Thank you
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> *Enrico Olivelli*
>> Software Development Manager @Diennea
>> Tel.: (+39) 0546 066100 - Int. 925
>> Viale G.Marconi 30/14 - 48018 Faenza (RA)
>>
>> MagNews - E-mail Marketing Solutions
>> http://www.magnews.it
>> Diennea - Digital Marketing Solutions
>> http://www.diennea.com
>>
>>
>>
>>
>>
>> ------------------------------
>> Rimani aggiornato sul mondo dell’email marketing e del digital marketing:
>> visita il nostro blog! http://blog.magnews.it
>>
>
>