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.1365200...@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/
