Horton

Thread

Commits

  1. Fix use of cursor sensitivity terminology

  1. cursor sensitivity misunderstanding

    Peter Eisentraut <peter.eisentraut@enterprisedb.com> — 2021-02-18T16:00:28Z

    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?

  2. Re: cursor sensitivity misunderstanding

    David G. Johnston <david.g.johnston@gmail.com> — 2021-02-18T16:11:27Z

    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;
"

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.

  3. Re: cursor sensitivity misunderstanding

    Peter Eisentraut <peter.eisentraut@enterprisedb.com> — 2021-02-18T18:14:30Z

    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.

  4. Re: cursor sensitivity misunderstanding

    Peter Eisentraut <peter.eisentraut@enterprisedb.com> — 2021-02-25T15:37:02Z

    On 18.02.21 19:14, Peter Eisentraut wrote:
> 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.

I came up with the attached patch to sort this out a bit.  It does not 
change any cursor behavior.  But the documentation now uses the terms 
more correctly and explains the differences between SQL and the 
PostgreSQL implementation better, I think.

  5. Re: cursor sensitivity misunderstanding

    David G. Johnston <david.g.johnston@gmail.com> — 2021-03-08T23:22:31Z

    On Thu, Feb 25, 2021 at 8:37 AM Peter Eisentraut <
peter.eisentraut@enterprisedb.com> wrote:

> On 18.02.21 19:14, Peter Eisentraut wrote:
> > 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.
>
> I came up with the attached patch to sort this out a bit.  It does not
> change any cursor behavior.  But the documentation now uses the terms
> more correctly and explains the differences between SQL and the
> PostgreSQL implementation better, I think.
>

thanks!, though this seems like the wrong approach.  Simply noting that our
cursor is not standard compliant (or at least we don't implement a
standard-compliant sensitive cursor) should suffice.  I don't really get
the point of adding ASENSITIVE if we don't have SENSITIVE too.  I'm also
unfamiliar with the standard default behaviors to comment on where we
differ there - but that should be easy enough to address.

I would suggest limiting the doc change to pointing out that we do allow
for a standard-compliant INSENSITIVE behaving cursor - one that precludes
local sensitively via the FOR SHARE and FOR UPDATE clauses - by adding that
keyword.  Otherwise, while the cursor is still (and always) insensitive
globally the cursor can become locally sensitive implicitly by including a
FOR UPDATE or FOR SHARE clause in the query.  Then maybe consider improving
the notes section through subtraction once a more clear initial
presentation has been made to the reader.

David J.

  6. Re: cursor sensitivity misunderstanding

    Peter Eisentraut <peter.eisentraut@enterprisedb.com> — 2021-03-11T22:02:34Z

    On 09.03.21 00:22, David G. Johnston wrote:
>     I came up with the attached patch to sort this out a bit.  It does not
>     change any cursor behavior.  But the documentation now uses the terms
>     more correctly and explains the differences between SQL and the
>     PostgreSQL implementation better, I think.
> 
> 
> thanks!, though this seems like the wrong approach.  Simply noting that 
> our cursor is not standard compliant (or at least we don't implement a 
> standard-compliant sensitive cursor) should suffice.

Well, we could just say, our behavior wrong/different.  But I think it's 
actually right, we were just looking at an incorrect premise and making 
additional claims about it that are not accurate.

> I don't really get 
> the point of adding ASENSITIVE if we don't have SENSITIVE too.  I'm also 
> unfamiliar with the standard default behaviors to comment on where we 
> differ there - but that should be easy enough to address.

ASENSITIVE is merely a keyword to select the default behavior.  Other 
SQL implementations also have it, so it seems sensible to add it while 
we're polishing this.