Re: Allows Extend Protocol support CURSOR_OPT_HOLD with prepared stmt.

On Wed, Aug 12, 2020 at 5:54 PM Dave Cramer <davecramer@postgres.rocks> wrote:

On Tue, 11 Aug 2020 at 22:33, Andy Fan <zhihui.fan1213@gmail.com> wrote:

On Mon, Jul 27, 2020 at 11:57 AM Andy Fan <zhihui.fan1213@gmail.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.