Allows Extend Protocol support CURSOR_OPT_HOLD with prepared stmt.

From: Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Allows Extend Protocol support CURSOR_OPT_HOLD with prepared stmt.
Date: 2020-07-27 03:52:42
Message-ID: CAKU4AWqvwmo=NLPGa_OHXB4F+u4Ts1_3YRy9M6XTjLt9DKHvvg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-jdbc

I have a user case like this:

rs = prepared_stmt.execute(1);
while(rs.next())
{
// do something with the result and commit the transaction.
conn.commit();
}

The driver used the extended protocol in this case. It works like this: 1).
Parse ->
PreparedStmt. 2). Bind -> Bind the prepared stmt with a Portal, no chance
to
set the CURSOR_OPT_HOLD option. 3). Execute. 4). Commit - the portal was
dropped at this stage. 5). when fetching the next batch of results, we get
the error
"Portal doesn't exist"

There are several methods we can work around this, but no one is perfect.
1.run the prepared stmt in a dedicated connection. (The number of
connection will
doubled)
2. use the with hold cursor. It doesn't support any bind parameter, so we
have
to create a cursor for each dedicated id.
3. don't commit the transaction. -- long transaction with many rows locked.

I have several questions about this case:
1. How about filling a cursorOptions information in bind protocol? then we
can
set the portal->cursorOptions accordingly? if so, how to be compatible
with the
old driver usually?
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?

--
Best Regards
Andy Fan

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Andy Fan 2020-07-27 03:57:19 Re: Allows Extend Protocol support CURSOR_OPT_HOLD with prepared stmt.
Previous Message Amit Kapila 2020-07-27 03:27:34 Re: INSERT INTO SELECT, Why Parallelism is not selected?

Browse pgsql-jdbc by date

  From Date Subject
Next Message Andy Fan 2020-07-27 03:57:19 Re: Allows Extend Protocol support CURSOR_OPT_HOLD with prepared stmt.
Previous Message Vladimir Sitnikov 2020-07-25 11:16:58 [pgjdbc/pgjdbc] 671558: chore: add S3 Gradle build cache