Re: Proposal to allow setting cursor options on Portals

Hi Dave,

The Company I working is in the progress to migrate a lot of databases
from Oracle to PostreSQL lately and have the need currently to hold
cursors over commits at the moment.

This does lead to nasty Java out-of-memory for big tables as in this
case the specified fetch size is been ignored.

There was also some thread around this issue here if I'm not mistaken:

https://www.postgresql.org/message-id/CAMT0RQSpqcvjHy3tam%2BnuKhoDH7XWBVy2mOp8J%3Df0dH30Rukew%40mail.gmail.com

I could also offer some type of I-the-field test as soon there is a way
to setup the needed environment locally...

Best regards

Patrick

Am 07.12.25 um 15:37 schrieb Dave Cramer:
> Greetings,
>
> My main driver here is to allow the creation of Holdable portals at
> the protocol level for drivers. Currently the only way to create a
> holdable cursor is at the SQL level.
>
> DECLARE liahona CURSOR WITH HOLD FOR SELECT * FROM films;
>
> The JDBC driver has an option in the API to have result sets survive
> commits see
> https://docs.oracle.com/javase/8/docs/api/java/sql/Connection.html#createStatement-int-int-int-
>
> Doing this at the protocol level is the correct way to do this as
> modifying the SQL to create a cursor is very cumbersome and we already
> have existing code to create a portal. Adding the ability to specify
> options
>
> Looking for feedback.
>
> Dave Cramer