Re: WHERE CURRENT OF behaviour is not what's documented

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/