Re: Allows Extend Protocol support CURSOR_OPT_HOLD with prepared stmt.

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

>
>
> 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.
>
>
Ya I thought about this after I answered it. If you have a resultset that
you requested in a transaction and then you commit the transaction I think
it is reasonable to expect that the resultset is no longer valid.

Dave Cramer
www.postgres.rocks

>