Request a Demo
Home   >   mailing lists

Re: Proposal to allow setting cursor options on Portals - Mailing list pgsql-hackers

From Patrick Reinhart
Subject Re: Proposal to allow setting cursor options on Portals
Date
Msg-id 2b3d5da9-34ba-49ee-a052-b6dd7233c524@gmail.com
Whole thread Raw
In response to Proposal to allow setting cursor options on Portals  (Dave Cramer <davecramer@gmail.com>)
List pgsql-hackers
Tree view
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


pgsql-hackers by date:

Previous
From: Corey Huinker
Date:
Subject: Re: Add starelid, attnum to pg_stats and leverage this in pg_dump
Next
From: Sami Imseih
Date:
Subject: Re: Add starelid, attnum to pg_stats and leverage this in pg_dump