Обсуждение: WHERE CURRENT OF behaviour is not what's documented
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>
On 2013-09-18 14:23:19 +0200, Boszormenyi Zoltan wrote: > 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: I think it's not referring to the behaviour inside a single session but across multiple sessions. I.e. when we follow the ctid chain of a tuple updated in a concurrent session. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
2013-09-18 14:27 keltezéssel, Andres Freund írta: > On 2013-09-18 14:23:19 +0200, Boszormenyi Zoltan wrote: >> 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: > I think it's not referring to the behaviour inside a single session but > across multiple sessions. I.e. when we follow the ctid chain of a tuple > updated in a concurrent session. But the documentation doesn't spell it out. Perhaps a little too terse. Quoting the SQL2011 draft, 4.33.2 Operations on and using cursors, page 112: If a cursor is open, and the SQL-transaction in which the cursor was opened makes a significant change to SQL-data, then whether that change is visible through that cursor before it is closed is determined as follows: — If the cursor is insensitive, then significant changes are not visible. — If the cursor is sensitive, then significant changes are visible. — If the cursor is asensitive, then the visibility of significant changes is implementation-dependent. SQL2003 has the same wording in 4.32.2 Operations on and using cursors on page 96. So, a SENSITIVE cursor shows "significant changes" (I guess a modified row counts as one) and they should be shown in the _same_ transaction where the cursor was opened. If anything, the PostgreSQL cursor implementation for FOR SHARE/FOR UPDATE is "asensitive". Also, "14.10 <update statement: positioned>", paragraph 14) in General Rules in SQL2003 (page 848) or "15.6 Effect of a positioned update", paragraph 16) in SQL2011 draft (page 996) says the new row replaces the old row *in the cursor*, not just in the table. Quote: " Let R1 be the candidate new row and let R be the current row of CR. ... The current row R of CR is replaced by R1. " 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/