Re: We don't enforce NO SCROLL cursor restrictions

Vik Fearing <vik(at)postgresfriends(dot)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