Re: The statement is re-executed (performed twice) on commit if it is declared as "cursor with hold" and the cursor is not closed yet

This is how cursors with hold work *NOW*. And this is the flaws of current
implementation.
It is possible to agree with these statements.

But it is impossible to agree that they *SHOULD* work this way.
At a minimum, this is neither obvious nor logical, and also not documented
anywhere. I think for many it would be a big surprise.

It is obvious that the DBMS needs to materialize the result of the query at
the time of the end of the transaction, but for this it is not necessary to
re-execute the query again.
It could begin to materialize at the moment of opening the cursor (if it is
scrollable), and at the end of the transaction - materialize its rest, for
example, by fetching the rest of cursor, or implement the ability the
reading data from the point of view of the current transaction.

With regards to the reasons for using the cursor.
At the moment, it is a necessary measure, because Postgres does not provide
alternatives for calling functions from the client, with INOUT or OUT
parameters, with the further possibility of obtaining modified values after
execution.
- perform statement - is not allowed from clients
- unnamed blocks - do not allow to work with parameters at all (neither
before execution, nor after)

пт, 12 июл. 2019 г. в 23:33, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:

> Rashid Abzalov <rashid(dot)abzalov(at)gmail(dot)com> writes:
> > The statement is re-executed on commit if it is declared as "cursor with
> > hold" and the cursor is not closed yet.
>
> That is not a bug, it's how cursors with hold work.
>
> (Volatile functions in cursors are a pretty fraught issue all around.
> I do not think we make very many guarantees about how often they'll
> be executed, if you do any re-reading or re-positioning of the cursor.)
>
> regards, tom lane
>