On Sun, 8 Nov 2026 at 22:29, Stef Van Vlierberghe <[email protected]> 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
