Re: Allows Extend Protocol support CURSOR_OPT_HOLD with prepared stmt.

On Mon, Jul 27, 2020 at 11:57 AM Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com> wrote:

>
>> 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?

--
Best Regards
Andy Fan