pgsql: Avoid misbehavior when persisting a non-stable cursor.

Avoid misbehavior when persisting a non-stable cursor.

PersistHoldablePortal has long assumed that it should store the
entire output of the query-to-be-persisted, which requires rewinding
and re-reading the output. This is problematic if the query is not
stable: we might get different row contents, or even a different
number of rows, which'd confuse the cursor state mightily.

In the case where the cursor is NO SCROLL, this is very easy to
solve: just store the remaining query output, without any rewinding,
and tweak the portal's cursor state to match. Aside from removing
the semantic problem, this could be significantly more efficient
than storing the whole output.

If the cursor is scrollable, there's not much we can do, but it
was already the case that scrolling a volatile query's result was
pretty unsafe. We can just document more clearly that getting
correct results from that is not guaranteed.

There are already prohibitions in place on using SCROLL with
FOR UPDATE/SHARE, which is one way for a SELECT query to have
non-stable results. We could imagine prohibiting SCROLL when
the query contains volatile functions, but that would be
expensive to enforce. Moreover, it could break applications
that work just fine, if they have functions that are in fact
stable but the user neglected to mark them so. So settle for
documenting the hazard.

While this problem has existed in some guise for a long time,
it got a lot worse in v11, which introduced the possibility
of persisting plpgsql cursors (perhaps implicit ones) even
when they violate the rules for what can be marked WITH HOLD.
Hence, I've chosen to back-patch to v11 but not further.

Per bug #17050 from Алексей Булгаков.

Discussion: https://postgr.es/m/17050-f77aa827dc85247c@postgresql.org

Branch
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/ba2c6d6cec000f0aeaeda4d56a23a335f6164860

Modified Files
--------------
doc/src/sgml/plpgsql.sgml | 9 ++++
doc/src/sgml/ref/declare.sgml | 8 ++--
src/backend/commands/portalcmds.c | 19 ++++++--
.../plpgsql/src/expected/plpgsql_transaction.out | 51 ++++++++++++++++++++++
src/pl/plpgsql/src/sql/plpgsql_transaction.sql | 41 +++++++++++++++++
5 files changed, 122 insertions(+), 6 deletions(-)