Re: Allows Extend Protocol support CURSOR_OPT_HOLD with prepared stmt.

On Wed, Aug 12, 2020 at 8:21 PM Dave Cramer <davecramer(at)postgres(dot)rocks>
wrote:

>
>
> 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.
>
>
I checked JDBC, the resultset only uses memory to cache the resultset.
so we can't set an inf+ fetch size with the hope that the client's
resultset
can cache all of them for us.

Basically I will use my above hack.

--
Best Regards
Andy Fan