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?
--
Best Regards
Andy Fan
