Re: Allows Extend Protocol support CURSOR_OPT_HOLD with prepared stmt.

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
>
>
I knew this point before working on that, but I heard from my customer that
the size
would be pretty big, so I gave up on this idea (too early). However,
after working on
a Holdable solution, I see there is very little difference between caching
the result
on the server or client. If the drivers can use the tempfile as an extra
store, then
things will be better than the server. Or else, things will be still
complex. Thanks
for your reminder!

--
Best Regards
Andy Fan