I wrote: > [ pokes at it some more ... ] Hm, we let you do this: > ... > 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.
I dug in the archives. The above-quoted error message was added by me in 048efc25e, responding to Heikki's complaint here: https://www.postgresql.org/message-id/471F69FE.5000500%40enterprisedb.com What I now see is that I put that check at the wrong level. It successfully blocks off the case Heikki complained of: DROP TABLE IF EXISTS foo; CREATE TABLE foo (id integer); INSERT INTO foo SELECT a from generate_series(1,10) a; BEGIN; DECLARE c CURSOR FOR SELECT id FROM foo FOR UPDATE; FETCH 2 FROM c; UPDATE foo set ID=20 WHERE CURRENT OF c; FETCH RELATIVE 0 FROM c; COMMIT; The FETCH RELATIVE 0 fails with ERROR: cursor can only scan forward HINT: Declare it with SCROLL option to enable backward scan. However, if you replace that with the should-be-equivalent FETCH ABSOLUTE 2 FROM c; then what you get is not an error but id ---- 3 (1 row) which is for certain anomalous, because that is not the row you saw as being row 2 in the initial FETCH. The reason for this behavior is that the only-scan-forward check is in the relatively low-level function PortalRunSelect, which is passed a "forward" flag and a count. The place that interprets FETCH_ABSOLUTE and friends is DoPortalRunFetch, and what it's doing in this particular scenario is to rewind to start and fetch forwards, thus bypassing PortalRunSelect's error check. And, since the query is using FOR UPDATE, this table scan sees the row with ID=2 as already dead. (Its replacement with ID=20 has been installed at the end of the table, so while it would be visible to the cursor, it's not at the same position as before.) So basically, we *do* have this check and have done since 2007, but it's not water-tight for all variants of FETCH. I think tightening it up in HEAD and v14 is a no-brainer, but I'm a bit more hesitant about whether to back-patch into stable branches. regards, tom lane
