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)
Rashid Abzalov <rashid.abzalov@gmail.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