Re: ResultSet storing all rows with defaulftFetchSize to 5000

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? Does PostgreSQL somehow supports
"fetch across commit"?
What is your DB version?

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.

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...

Vladimir