Re: ResultSet storing all rows with defaulftFetchSize to 5000

On 12 February 2018 at 11:08, Jaime Soler <jaime(dot)soler(at)gmail(dot)com> wrote:

> Hi,
>
> 2018-02-12 16:45 GMT+01:00 Vladimir Sitnikov <sitnikov(dot)vladimir(at)gmail(dot)com>
> :
>
>> Jaime>I am helping to migrate a Oracle application to run on Postgresql,
>> and the default holdability setting at oracle is HOLD_CURSOR_OVER_COMMIT
>> so I have tried to set this HOLD_CURSOR_OVER_COMMIT as default
>> holdability setting
>>
>> Ok, that makes sense. I mean Oracle serves "fetch across commit" just
>> fine (of course you can hit ORA-01555, but that is another story)
>>
>> >-- after validating a chunk of data, continue with the next chunk
>> >postgres=# commit;
>> >postgres=# fetch next p;
>>
>> Wait. Did you just make up the output?
>>
>
> No. it's a test case on the server database.
>
>> Does PostgreSQL somehow supports "fetch across commit"?
>>
>
> Yes, using WITH HOLD.
>
>
>
>> What is your DB version?
>>
>
>
> PostgreSQL 10.1 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
> 6.3.0-12ubuntu2) 6.3.0 20170406, 64-bit
>
>
>
>>
>>
>>
>> I have no idea why PostgreSQL closes all the cursors as transaction ends, however
>> that is the documented behavior.
>>
>> https://www.postgresql.org/docs/10/static/plpgsql-cursors.html
>> 42.7.3. Using Cursors -> All portals are implicitly closed at transaction
>> end. Therefore a refcursor value is usable to reference an open cursor only
>> until the end of the transaction.
>>
>
> Well I am not using PL/PGSQL cursor, but I think postgresql doesn't close
> portal after commit if you use WITH HOLD.
>
> https://www.postgresql.org/docs/10/static/sql-declare.html
>
> WITH HOLD specifies that the cursor can continue to be used after the
> transaction that created it successfully commits.
>
> If WITH HOLD is specified and the transaction that created the cursor
> successfully commits, the cursor can continue to be accessed by subsequent
> transactions in the same session. (But if the creating transaction is
> aborted, the cursor is removed.) A cursor created with WITH HOLD is
> closed when an explicit CLOSE command is issued on it, or the session
> ends. In the current implementation, the rows represented by a held cursor
> are copied into a temporary file or memory area so that they remain
> available for subsequent transactions.
>
>
>
>>
>> pgjdbc just tries its best to avoid running into invalid (closed by
>> backend) cursors, and pgjdbc fetches all the contents in HOLD_CURSOR_OVER_COMMIT
>> case.
>>
>> It might be something to be discussed on pgsql-hackers mailing list.
>> If only the DB did not close the portals...
>>
>
>
> Don't you think that driver could use Portal and avoid storing all object
> when setFetchSize was set in a transaction ?
>

It does this if you don't use HOLD_CURSOR_OVER_COMMIT.

Dave Cramer

davec(at)postgresintl(dot)com
www.postgresintl.com

>
>