Re: Allows Extend Protocol support CURSOR_OPT_HOLD with prepared stmt.

On Tue, 11 Aug 2020 at 22:33, Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com> wrote:

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

I think if you set the fetch size the driver will use a named cursor and
this should work

Dave Cramer
www.postgres.rocks