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/
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
