Обсуждение: cursor sensitivity misunderstanding
I think our documentation is mistaken about what it means for a cursor to be "sensitive" or "insensitive". The definition in SQL:2016 is: A change to SQL-data is said to be independent of a cursor CR if and only if it is not made by an <update statement: positioned> or a <delete statement: positioned> that is positioned on CR. A change to SQL-data is said to be significant to CR if and only if it is independent of CR, and, had it been committed before CR was opened, would have caused the sequence of rows in the result set descriptor of CR to be different in any respect. ... 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. So I think a test case would be: create table t1 (a int); insert into t1 values (1); begin; declare c1 cursor for select * from t1; insert into t1 values (2); fetch next from c1; -- returns 1 fetch next from c1; -- ??? commit; With a sensitive cursor, the second fetch would return 2, with an insensitive cursor, the second fetch returns nothing. The latter happens with PostgreSQL. The DECLARE man page describes it thus: INSENSITIVE Indicates that data retrieved from the cursor should be unaffected by updates to the table(s) underlying the cursor that occur after the cursor is created. In PostgreSQL, this is the default behavior; so this key word has no effect and is only accepted for compatibility with the SQL standard. Which is not wrong, but it omits that this is only relevant for changes in the same transaction. Later in the DECLARE man page, it says: 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 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". And that seems definitely wrong. Declaring c1 in the above example as FOR UPDATE or FOR SHARE does not change the result. I think this discussion is mixing up the concept of cursor sensitivity with transaction isolation. Thoughts?
On Thu, Feb 18, 2021 at 9:00 AM Peter Eisentraut <peter.eisentraut@enterprisedb.com> wrote:
And that seems definitely wrong. Declaring c1 in the above example as
FOR UPDATE or FOR SHARE does not change the result. I think this
discussion is mixing up the concept of cursor sensitivity with
transaction isolation.
Thoughts?
This came up on Discord in the context of pl/pgsql last month - never really came to a conclusion.
"
open curs FOR SELECT * FROM Res FOR UPDATE;
LOOP
FETCH curs into record;
EXIT WHEN NOT FOUND;
INSERT INTO Res SELECT Type.Name
FROM Type
WHERE Type.SupClass = record.Name;
END LOOP;
LOOP
FETCH curs into record;
EXIT WHEN NOT FOUND;
INSERT INTO Res SELECT Type.Name
FROM Type
WHERE Type.SupClass = record.Name;
END LOOP;
"
The posted question was: "this doesn't go over rows added during the loop despite the FOR UPDATE"
The OP was doing a course based on Oracle and was confused regarding our behavior. The documentation failed to help me provide a useful response, so I'd agree there is something here that needs reworking if not outright fixing.
David J.
On 18.02.21 17:11, David G. Johnston wrote: > The OP was doing a course based on Oracle and was confused regarding our > behavior. The documentation failed to help me provide a useful > response, so I'd agree there is something here that needs reworking if > not outright fixing. According to the piece of the standard that I posted, the sensitivity behavior here is implementation-dependent (not even -defined), so both implementations are correct. But the poster was apparently also confused by the same piece of documentation. If you consider the implementation of MVCC in PostgreSQL, then the current behavior makes sense. I suspect that this consideration was much more interesting for older system with locking-based concurrency and where "read uncommitted" was a real thing. With the current system, insensitive cursors are essentially free and sensitive cursors would require quite a bit of effort to implement.