cursor sensitivity misunderstanding - Mailing list pgsql-hackers

From Peter Eisentraut
Subject cursor sensitivity misunderstanding
Date
Msg-id 96ee8b30-9889-9e1b-b053-90e10c050e85@enterprisedb.com
Whole thread Raw
Responses Re: cursor sensitivity misunderstanding
List pgsql-hackers
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?



pgsql-hackers by date:

Previous
From: Matthias van de Meent
Date:
Subject: Re: Improvements and additions to COPY progress reporting
Next
From: Andrew Dunstan
Date:
Subject: Re: Finding cause of test fails on the cfbot site