Re: We don't enforce NO SCROLL cursor restrictions

Vik Fearing <vik@postgresfriends.org> writes:
> On 9/9/21 7:10 PM, Tom Lane wrote:
>> 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?

> I would say yes.  NO SCROLL means no scrolling; or at least should.
> On the other hand, if there is no optimization or other meaningful
> difference between SCROLL and NO SCROLL, then we can just document it as
> a no-op that is only provided for standard syntax compliance.

There are definitely optimizations that happen or don't happen
depending on the SCROLL option.  I think ba2c6d6ce may be the
first patch that introduces any user-visible semantic difference,
but I'm not completely sure about that.

[ pokes at it some more ... ]  Hm, we let you do this:

regression=# begin;
BEGIN
regression=*# declare c cursor for select * from int8_tbl for update;
DECLARE CURSOR
regression=*# fetch all from c;
        q1        |        q2         
------------------+-------------------
              123 |               456
              123 |  4567890123456789
 4567890123456789 |               123
 4567890123456789 |  4567890123456789
 4567890123456789 | -4567890123456789
(5 rows)

regression=*# fetch absolute 2 from c;
 q1  |        q2        
-----+------------------
 123 | 4567890123456789
(1 row)

which definitely flies in the face of the fact that we disallow
combining SCROLL and FOR UPDATE:

regression=*# declare c scroll cursor for select * from int8_tbl for update;
ERROR:  DECLARE SCROLL CURSOR ... FOR UPDATE is not supported
DETAIL:  Scrollable cursors must be READ ONLY.

I don't recall the exact reason for that prohibition, but I wonder
if there aren't user-visible anomalies reachable from the fact that
you can bypass it.

            regards, tom lane