From: Tom Lane <tgl@sss.pgh.pa.us>
To: pgsql-hackers@lists.postgresql.org
Subject: We don't enforce NO SCROLL cursor restrictions
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3712910.1631207435.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Thu, 09 Sep 2021 13:10:35 -0400
Message-ID: <3712911.1631207435@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/3712911.1631207435%40sss.pgh.pa.us>
Precedence: bulk

We don't actually prevent you from scrolling a NO SCROLL cursor:

regression=3D# begin;
BEGIN
regression=3D*# declare c no scroll cursor for select * from int8_tbl;
DECLARE CURSOR
regression=3D*# fetch all from c;
        q1        |        q2         =

------------------+-------------------
              123 |               456
              123 |  4567890123456789
 4567890123456789 |               123
 4567890123456789 |  4567890123456789
 4567890123456789 | -4567890123456789
(5 rows)

regression=3D*# fetch absolute 2 from c;
 q1  |        q2        =

-----+------------------
 123 | 4567890123456789
(1 row)

There are probably specific cases where you do get an error,
but we don't have a blanket you-can't-do-that check.  Should we?

The reason this came to mind is that while poking at [1]
I noticed that commit ba2c6d6ce has created some user-visible
anomalies for non-scrollable cursors WITH HOLD.  If you advance
the cursor a bit, commit, and then try to scroll the cursor,
it will work but the part of the output that you advanced over
will be missing.  I think we should probably throw an error
to prevent that from being visible.  I'm worried though that
putting in a generic prohibition may break applications that
used to get away with this kind of thing.

			regards, tom lane

[1] https://www.postgresql.org/message-id/CAPV2KRjd%3DErgVGbvO2Ty20tKTEZZr=
6cYsYLxgN_W3eAo9pf5sw%40mail.gmail.com