From: Tom Lane <tgl@sss.pgh.pa.us>
To: Vik Fearing <vik@postgresfriends.org>
Cc: pgsql-hackers@lists.postgresql.org
Subject: Re: We don't enforce NO SCROLL cursor restrictions
Date: Thu, 09 Sep 2021 14:09:58 -0400
Message-ID: <3722175.1631210998@sss.pgh.pa.us> (raw)
In-Reply-To: <e6b5e1c3-71ad-b384-7f6f-0c3f021906d1@postgresfriends.org>
References: <3712911.1631207435@sss.pgh.pa.us>
<e6b5e1c3-71ad-b384-7f6f-0c3f021906d1@postgresfriends.org>
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
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 using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: tgl@sss.pgh.pa.us, vik@postgresfriends.org, pgsql-hackers@lists.postgresql.org
Subject: Re: We don't enforce NO SCROLL cursor restrictions
In-Reply-To: <3722175.1631210998@sss.pgh.pa.us>
* 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