Re: Proposal to allow setting cursor options on Portals

On Sun, 8 Nov 2026 at 22:29, Stef Van Vlierberghe
<stef(dot)van-vlierberghe(at)telenet(dot)be> wrote:
>
> Hi Vladimir and Dave et al.
>
> Sorry to invade you thread, apologies if this is inapproriate. ChatGPT pointed me to this discussion and suggested to respond with below contribution. Would like to add a perspective from an existing Oracle OCI application that we are considering migrating to PostgreSQL.
>
> First, I appreciate the effort going into this proposal. I can see that there are quite a few difficult details to get right, particularly around protocol compatibility, holdable cursors, scrollability and connection poolers.
>
> However, I wonder whether you might be trying to solve too many problems at once, potentially delaying functionality that would already be extremely useful to a significant class of applications.
>
> Our requirements are actually rather modest compared with the full scope being discussed.
>
> Each application process maintains a collection of roughly 100–200 prepared SQL statements, which are reused many times with different bind values. Usually, an execution is performed and consumed before moving on to the next one. Performance is critical.
>
> However, in some parts of the application, we have 2–10 SELECT cursors active simultaneously. A single application thread decides which cursor to consume next. Each cursor maintains its own execution state, and fetching is done in bounded batches (similar to OCI array fetching).
>
> We need:
>
> Prepare each SQL statement once per connection.
> Execute it repeatedly with different binary bind values.
> Create multiple independent, named execution portals from the same prepared statement.
> Fetch from whichever portal the application chooses, with a configurable maximum number of rows per fetch.
> Resume a portal later, without losing its execution state.
> Close each portal independently.
> Avoid materializing complete result sets in client memory.
>
> We only need forward-only, transaction-scoped cursors. We don't need scrollability, holdability across transactions, or concurrent execution of queries on the server. Sequential execution of portal operations on a connection is perfectly acceptable, provided the application controls which portal is executed next.
>
> We have approximately 40 application processes, each using 2–3 pthreads. Using one database connection per application thread would be perfectly reasonable for us. Connection pooling and multiplexing are not the issue.
>
> This is a perfectly natural programming model with Oracle OCI, and the absence of an equivalent basic API is likely to become an obstacle to migrating this application to PostgreSQL. We probably are not be the only potential PostgreSQL adopters in this situation.
>
> Perhaps it would be worth considering a smaller initial API that supports the essential forward-only portal operations, leaving scrollability, holdability and other more elaborate functionality for subsequent extensions.
>
> Could you maybe first provide the basic building blocks that allow applications to use PostgreSQL portals as independently managed cursors, without requiring every possible cursor feature and use case to be addressed at the same time?
>
> I realize that a clean and extensible API is important, and I don't want to underestimate the protocol and implementation complexities involved, just wondering whether the pursuit of a fully comprehensive solution might inadvertently keep a useful, relatively basic capability out of reach for applications that need it.
>
> Note : I consulted Claude, Gemini and ChatGPT. Gemini suggested to use the "solution" below :
>
> <<
>
> // 2. Prepare statement once per connection
> res = PQprepare(
> conn,
> "user_query",
> "SELECT * FROM users WHERE status = $1 AND region = $2",
> 2,
> NULL
> );
> if (PQresultStatus(res) != PGRES_COMMAND_OK) {
> fprintf(stderr, "Prepare failed: %s\n", PQerrorMessage(conn));
> }
> PQclear(res);
>
> // 3. Declare cursor using PQexecParams with bind values
> const char *paramValues[2] = {"1", "US-WEST"};
> res = PQexecParams(
> conn,
> "DECLARE cur_1 CURSOR FOR EXECUTE user_query($1, $2)",
> 2, NULL, paramValues, NULL, NULL, 0
> );
> PQclear(res);
>
> >>
>
> And this looked nice but ChatGPT clarified this is not supported :
>
> <<
> PostgreSQL does not allow EXECUTE (the SQL prepared-statement command) as the query expression of DECLARE CURSOR.
> >>
>
> Both Claude and ChatGPT explained that the protocol supports what I want, but libpq does not provide an API, and this lead to your discussion.
>
> Am just exploring feasibility of migration to postgresql, so still far from evaluating performance, and so will drop the requirement to parse the SQL only once, using a binary version of the sample code below :
>
> <<
> PQexec(conn, "BEGIN");
>
> const char *params1[] = {"1", "US-WEST"};
> const char *params2[] = {"1", "EU-WEST"};
>
> PGresult *res;
>
> res = PQexecParams(conn,
> "DECLARE cur_1 NO SCROLL CURSOR FOR "
> "SELECT * FROM users WHERE status = $1 AND region = $2",
> 2, NULL, params1, NULL, NULL, 0);
>
> >>
>
> Yet, if an upcoming version of libpq would handle the above this would be most welcome, even if it would come with many limitations (which, I believe, would not matter for our use-case).
>
>
> My prompt was :
>
> Large (2M lines) Ada application uses Oracle OCI via binding that supports creating multiple selecting cursors on a single connection using (binary) bind variables, it can then set bind values to the variables and execute several cursors in parallel, and consume rows from the individual cursors while the OCI layer keeps all fetched data available in buffer memory until rows are consumed. I want the sql parsed once and executed multiple times (so repeatedly set new bind values and execute without providing the sql each time). The total amount of data read can be huge so I do not want to buffer the entire result in client memory, I believe this implies the use of server-side cursors. The Oracle OCI implementation is based on array-fetch, buffering a max nr of rows for each OCI call to fetch the next batch. Real parallelism on the server is not essential (application rarely uses oracle parallel query execution). The application must be able to choose which cursor to consume when. Many clients execute this application in parallel, so each constructs the same sql statements, Oracle uses a cursor cache on the server (SQL area) to avoid repeated identical work. The clients cannot coordinate to allocate some wrapper functions that need to have a unique name in the database. All info is supposed to stream from the DB server to the client, never storing the entire result. How do I map this to postgresql libpq ? I understand libpq will always manage itself the buffer memory and copy info read from the connection to its own buffers. I want the application copying this data directly from the libpq buffers, i.e. I don't want a second layer in the binding to copy from libpq and then later copy to the application when it requests a row from a specific cursor. Not sure the libpq PREPARE/DECLARE can accomplish the above. It looks like pipeline mode lets the server decide for which cursor/select to send rows when, not sure if the application can access these rows from libpq when it is ready to do so. Especially PQGetResult (Conn) looks like a non-starter as it does not let the application associate one result with one specific select. Using one connection per cursor is likely inefficient, will start a process for each connection (so for each cursor), and for each application, ending up consuming a lot of process/memory resources.
>
> No need to respond to my message but I remain available in case you have questions.
>
> Thanks again for working on this, I am personally a big fan of open-source software.
>
> All the best,
>
> Stef

HI Stef,

So my interest in changing the protocol is to make it easier for
drivers such as the JDBC driver to implement cursors with hold and
scrollable cursors. I see that your interest is to add new
functionality to the cursor API to be more compatible with Oracle.
Today everything I am looking to implement is possible using SQL.

Thanks,
Dave