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?
I forget to say in this case, the user has to drop the holdable portal explicitly.
After some days's hack and testing, I found more issues to support the following case
rs = prepared_stmt.execute(1);
while(rs.next())
{
// do something with the result (mainly DML )
conn.commit(); or conn.rollback();
// commit / rollback to avoid the long lock holding.
}
The holdable portal is still be dropped in transaction aborted/rollbacked case since
the HoldPortal doesn't happens before that and "abort/rollabck" means something
wrong so it is risk to hold it again. What I did to fix this issue is HoldPortal just after
we define a Holdable portal. However, that's bad for performance. Originally, we just
needed to scan the result when needed, now we have to hold all the results and then fetch
and the data one by one.
The above user case looks reasonable to me IMO, I would say it is kind of "tech debt"
in postgres. To support this completely, looks we have to decouple the snapshot/locking
management with transaction? If so, it looks like a huge change. I wonder if anybody
tried to resolve this issue and where do we get to that point?