Re: ResultSet storing all rows with defaulftFetchSize to 5000

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 ?

Regards

>
>
>
>
>
> Vladimir
>