I have a user case like this:
rs = prepared_stmt.execute(1);
while(rs.next())
{
// do something with the result and commit the transaction.
conn.commit();
}
The driver used the extended protocol in this case. It works like this: 1). Parse ->
PreparedStmt. 2). Bind -> Bind the prepared stmt with a Portal, no chance to
set the CURSOR_OPT_HOLD option. 3). Execute. 4). Commit - the portal was
dropped at this stage. 5). when fetching the next batch of results, we get the error
"Portal doesn't exist"
There are several methods we can work around this, but no one is perfect.
1.run the prepared stmt in a dedicated connection. (The number of connection will
doubled)
2. use the with hold cursor. It doesn't support any bind parameter, so we have
to create a cursor for each dedicated id.
3. don't commit the transaction. -- long transaction with many rows locked.
I have several questions about this case:
1. How about filling a cursorOptions information in bind protocol? then we can
set the portal->cursorOptions accordingly? if so, how to be compatible with the
old driver usually?
2. Currently I want to add a new GUC parameter, if set it to true, server will
create a holdable portal, or else nothing changed. Then let the user set
it to true in the above case and reset it to false afterward. Is there any issue
with this method?