Re: Proposal to allow setting cursor options on Portals

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