From: Tom Lane <tgl@sss.pgh.pa.us>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: Fix some anomalies with NO SCROLL cursors.
Date: Fri, 10 Sep 2021 17:18:55 +0000
Message-ID: <E1mOkAx-0002Mh-NT@gemulon.postgresql.org> (raw)
Fix some anomalies with NO SCROLL cursors.
We have long forbidden fetching backwards from a NO SCROLL cursor,
but the prohibition didn't extend to cases in which we rewind the
query altogether and then re-fetch forwards. I think the reason is
that this logic was mainly meant to protect plan nodes that can't
be run in the reverse direction. However, re-reading the query output
is problematic if the query is volatile (which includes SELECT FOR
UPDATE, not just queries with volatile functions): the re-read can
produce different results, which confuses the cursor navigation logic
completely. Another reason for disliking this approach is that some
code paths will either fetch backwards or rewind-and-fetch-forwards
depending on the distance to the target row; so that seemingly
identical use-cases may or may not draw the "cursor can only scan
forward" error. Hence, let's clean things up by disallowing rewind
as well as fetch-backwards in a NO SCROLL cursor.
Ordinarily we'd only make such a definitional change in HEAD, but
there is a third reason to consider this change now. Commit ba2c6d6ce
created some new user-visible anomalies for non-scrollable cursors
WITH HOLD, in that navigation in the cursor result got confused if the
cursor had been partially read before committing. The only good way
to resolve those anomalies is to forbid rewinding such a cursor, which
allows removal of the incorrect cursor state manipulations that
ba2c6d6ce added to PersistHoldablePortal.
To minimize the behavioral change in the back branches (including
v14), refuse to rewind a NO SCROLL cursor only when it has a holdStore,
ie has been held over from a previous transaction due to WITH HOLD.
This should avoid breaking most applications that have been sloppy
about whether to declare cursors as scrollable. We'll enforce the
prohibition across-the-board beginning in v15.
Back-patch to v11, as ba2c6d6ce was.
Discussion: https://postgr.es/m/3712911.1631207435@sss.pgh.pa.us
Branch
------
REL_14_STABLE
Details
-------
https://git.postgresql.org/pg/commitdiff/d844cd75a6764a1dd2d8b3410144df9ebf8a0f04
Modified Files
--------------
src/backend/commands/portalcmds.c | 28 ++++++++++++++++++--------
src/backend/tcop/pquery.c | 33 ++++++++++++++++++++++++++++---
src/test/regress/expected/portals.out | 37 +++++++++++++++++++++++++++++++++++
src/test/regress/sql/portals.sql | 20 +++++++++++++++++++
4 files changed, 107 insertions(+), 11 deletions(-)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-committers@postgresql.org
Cc: tgl@sss.pgh.pa.us
Subject: Re: pgsql: Fix some anomalies with NO SCROLL cursors.
In-Reply-To: <E1mOkAx-0002Mh-NT@gemulon.postgresql.org>
References: <E1mOkAx-0002Mh-NT@gemulon.postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox