WHERE CURRENT OF behaviour is not what's documented

Hi,

I have experimented with cursors a little and found that the part about FOR SHARE/FOR
UPDATE in

http://www.postgresql.org/docs/9.2/interactive/sql-declare.html

i.e. the "sensitive cursor" is not what actually happens. BTW, 9.3 has the same contents
for the same page.

"
If the cursor's query includes FOR UPDATE or FOR SHARE, then returned rows are locked at
the time they are first fetched, in the same way as for a regular SELECT
<http://www.postgresql.org/docs/9.3/interactive/sql-select.html> command with these
options. In addition, the returned rows will be the most up-to-date versions; therefore
these options provide the equivalent of what the SQL standard calls a "sensitive cursor".
(Specifying INSENSITIVE together with FOR UPDATE or FOR SHARE is an error.)
"

The statement that the "most up-to-date versions of the rows are returned"
doesn't reflect the reality anymore:

$ psql
psql (9.2.4)
Type "help" for help.

zozo=> create table xxx (id serial primary key, t text);
NOTICE: CREATE TABLE will create implicit sequence "xxx_id_seq" for serial column "xxx.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "xxx_pkey" for table "xxx"
CREATE TABLE
zozo=> insert into xxx (t) values ('a'), ('b'), ('c');
INSERT 0 3
zozo=> begin;
BEGIN
zozo=> declare mycur cursor for select * from xxx for update;
DECLARE CURSOR
zozo=> fetch all from mycur;
id | t
----+---
1 | a
2 | b
3 | c
(3 rows)

zozo=> move absolute 0 in mycur;
MOVE 0
zozo=> fetch from mycur;
id | t
----+---
1 | a
(1 row)

zozo=> update xxx set t = t || '_x' where current of mycur;
UPDATE 1
zozo=> move absolute 0 in mycur;
MOVE 0
zozo=> fetch all from mycur;
id | t
----+---
2 | b
3 | c
(2 rows)

What happened to the "most up-to-date row" of "id == 1"?

zozo=> select * from xxx where id = 1;
id | t
----+-----
1 | a_x
(1 row)

The same behaviour is experienced under 9.2.4 and 9.3.0.

As a side note, I couldn't test 8.4.17, 9.0.13 and 9.1.9 under Fedora 19,
because initdb fails for all 3 versions. I am bitten by the same as what's
described here: http://www.postgresql.org/message-id/14242.1365200084@sss.pgh.pa.us

It the above cursor behaviour is the accepted/expected one?

Since SCROLL (with or without INSENSITIVE) cannot be specified together
with FOR UPDATE/FOR SHARE, I know the MOVE ABSOLUTE 0 is on the
verge of being invalid in this case.

But in any case, either the documentation should tell that the UPDATEd
rows will be missing from a reset executor run or MOVE ABSOLUTE
with a value smaller than portal->portalPos should also be refused
just like MOVE BACKWARD.

As another side note, portal->portalPos mentions it can overflow,
so I suggest using int64 explicitly, so it's ensured that 32-bit systems
get the same overflow behaviour as 64-bit ones. Or (the horror, the horror!) int128_t.

Best regards,
Zoltán Böszörményi

--
----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de
http://www.postgresql.at/