Re: Allows Extend Protocol support CURSOR_OPT_HOLD with prepared stmt.

On Wed, Aug 12, 2020 at 8:11 PM Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com> wrote:

>
>
> On Wed, Aug 12, 2020 at 5:54 PM Dave Cramer <davecramer(at)postgres(dot)rocks>
> wrote:
>
>>
>>
>>
>> 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
>>
>>
> If the drivers can use the tempfile as an extra store, then things will be
> better than the server.
>

Maybe not much better, just the same as each other. Both need to
store all of them first and fetch them from the temp store again.

--
Best Regards
Andy Fan