Re: Allows Extend Protocol support CURSOR_OPT_HOLD with prepared stmt.
| От | Andy Fan |
|---|---|
| Тема | Re: Allows Extend Protocol support CURSOR_OPT_HOLD with prepared stmt. |
| Дата | |
| Msg-id | CAKU4AWoJXnLVe=N2d5ED64-7B0-xHkKbzP_tUhhtW5dDdJnN0A@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Allows Extend Protocol support CURSOR_OPT_HOLD with prepared stmt. (Andy Fan <zhihui.fan1213@gmail.com>) |
| Ответы |
Re: Allows Extend Protocol support CURSOR_OPT_HOLD with prepared stmt.
|
| Список | pgsql-hackers |
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 willcreate a holdable portal, or else nothing changed. Then let the user setit to true in the above case and reset it to false afterward. Is there any issuewith 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();
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
В списке pgsql-hackers по дате отправления: