Re: WHERE CURRENT OF behaviour is not what's documented - Mailing list pgsql-hackers
From | Boszormenyi Zoltan |
---|---|
Subject | Re: WHERE CURRENT OF behaviour is not what's documented |
Date | |
Msg-id | 5239B30D.5060409@cybertec.at Whole thread Raw |
In response to | Re: WHERE CURRENT OF behaviour is not what's documented (Andres Freund <andres@2ndquadrant.com>) |
List | pgsql-hackers |
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/
pgsql-hackers by date: