WHERE CURRENT OF behaviour is not what's documented - Mailing list pgsql-hackers

Hi,<br /><br /> I have experimented with cursors a little and found that the part about FOR SHARE/FOR UPDATE in<br
/><br/><a class="moz-txt-link-freetext"
href="http://www.postgresql.org/docs/9.2/interactive/sql-declare.html">http://www.postgresql.org/docs/9.2/interactive/sql-declare.html</a><br
/><br/> i.e. the "sensitive cursor" is not what actually happens. BTW, 9.3 has the same contents for the same page.<br
/><br/> "<br /> If the cursor's query includes <tt class="LITERAL">FOR UPDATE</tt> or <tt class="LITERAL">FOR
SHARE</tt>,then returned rows are locked at the time they are first fetched, in the same way as for a regular <a
href="http://www.postgresql.org/docs/9.3/interactive/sql-select.html">SELECT</a>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
theSQL standard calls a <span class="QUOTE">"sensitive cursor"</span>. (Specifying <tt class="LITERAL">INSENSITIVE</tt>
togetherwith <tt class="LITERAL">FOR UPDATE</tt> or <tt class="LITERAL">FOR SHARE</tt> is an error.)<br /> "<br /><br
/>The statement that the "most up-to-date versions of the rows are returned"<br /> doesn't reflect the reality
anymore:<br/><br /> $ psql<br /> psql (9.2.4)<br /> Type "help" for help.<br /><br /> zozo=> create table xxx (id
serialprimary key, t text);<br /> NOTICE:  CREATE TABLE will create implicit sequence "xxx_id_seq" for serial column
"xxx.id"<br/> NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "xxx_pkey" for table "xxx"<br /> CREATE
TABLE<br/> zozo=> insert into xxx (t) values ('a'), ('b'), ('c');<br /> INSERT 0 3<br /> zozo=> begin;<br />
BEGIN<br/> zozo=> declare mycur cursor for select * from xxx for update;<br /> DECLARE CURSOR<br /> zozo=> fetch
allfrom mycur;<br />  id | t <br /> ----+---<br />   1 | a<br />   2 | b<br />   3 | c<br /> (3 rows)<br /><br />
zozo=>move absolute 0 in mycur;<br /> MOVE 0<br /> zozo=> fetch from mycur;<br />  id | t <br /> ----+---<br />  
1| a<br /> (1 row)<br /><br /> zozo=> update xxx set t = t || '_x' where current of mycur;<br /> UPDATE 1<br />
zozo=>move absolute 0 in mycur;<br /> MOVE 0<br /> zozo=> fetch all from mycur;<br />  id | t <br /> ----+---<br
/>  2 | b<br />   3 | c<br /> (2 rows)<br /><br /> What happened to the "most up-to-date row" of "id == 1"?<br /><br />
zozo=>select * from xxx where id = 1;<br />  id |  t  <br /> ----+-----<br />   1 | a_x<br /> (1 row)<br /><br />
Thesame behaviour is experienced under 9.2.4 and 9.3.0.<br /><br /> As a side note, I couldn't test 8.4.17, 9.0.13 and
9.1.9under Fedora 19,<br /> because initdb fails for all 3 versions. I am bitten by the same as what's<br /> described
here:<a class="moz-txt-link-freetext"
href="http://www.postgresql.org/message-id/14242.1365200084@sss.pgh.pa.us">http://www.postgresql.org/message-id/14242.1365200084@sss.pgh.pa.us</a><br
/><br/> It the above cursor behaviour is the accepted/expected one?<br /><br /> Since SCROLL (with or without
INSENSITIVE)cannot be specified together<br /> with FOR UPDATE/FOR SHARE, I know the MOVE ABSOLUTE 0 is on the<br />
vergeof being invalid in this case.<br /><br /> But in any case, either the documentation should tell that the
UPDATEd<br/> rows will be missing from a reset executor run or MOVE ABSOLUTE<br /> with a value smaller than
portal->portalPosshould also be refused<br /> just like MOVE BACKWARD.<br /><br /> As another side note,
portal->portalPosmentions it can overflow,<br /> so I suggest using int64 explicitly, so it's ensured that 32-bit
systems<br/> get the same overflow behaviour as 64-bit ones. Or (the horror, the horror!) int128_t.<br /><br /> Best
regards,<br/> Zoltán Böszörményi<br /><br /><pre class="moz-signature" cols="90">-- 
 
----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt, Austria
Web: <a class="moz-txt-link-freetext" href="http://www.postgresql-support.de">http://www.postgresql-support.de</a>
<aclass="moz-txt-link-freetext" href="http://www.postgresql.at/">http://www.postgresql.at/</a>
 
</pre>