Re: updateable cursors & visibility - Mailing list pgsql-hackers

Neil Conway wrote:
> On Mon, 2003-03-24 at 22:50, Hiroshi Inoue wrote:
> > Does the SQL standard allow INSENSITIVE updatable cursors ?
> 
> Hmmm... apparently not:
> 
> (Subsection 14.1, Syntax Rules of DECLARE CURSOR)
> 
> 11) If an <updatability clause> of FOR UPDATE with or without a <column
> name list> is specified, then INSENSITIVE shall not be specified and QE
> shall be updateable.
> 
> I'm not really sure I see the point of this restriction, though...

My guess is that while INSENSITIVE is good, when you use updatable
cursors, when you use WHERE CURRENT OF for UPDATE, you are really
updating the cursor.

However, I think it would be really weird to be seeing other people
changes (senstive) in my cursor, so I am not sure we really want to
follow that area of the spec.  If they said you should be able to see
the WHERE CURRENT OF updates, but not other people's commits, I could
see that logic.  I wonder if they did the spec this way because some
vendors couldn't do INSENSITIVE - WHERE CURRENT OF cursors.

I see SQL99 specifies SENSITIVE:
        <cursor sensitivity> ::=               SENSITIVE             | INSENSITIVE             | ASENSITIVE

Maybe we should add the keyword SENSITIVE and throw an error if
SENSITIVE is used because we don't support it.  Of course, we throw an
error now, but the error is "syntax error" rather than "not supported".

Here is the SQL99 standard on this:

---------------------------------------------------------------------------
        Another property of a cursor is its sensitivity, which may be        sensitive, insensitive, or asensitive,
dependingon whether        SENSITIVE, INSENSITIVE, or ASENSITIVE is specified or implied. The        following
paragraphsdefine several terms used to discuss issues        relating to cursor sensitivity:
 
        A change to SQL-data is said to be independent of a cursor CR if        and only if it is not made by an
<updatestatement: 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
beencommitted before CR was        opened, would have caused the table associated with the cursor to        be
differentin any respect.
 
        A change to SQL-data is said to be visible to CR if and only if it        has an effect on CR by inserting a
rowin CR, deleting a row from        CR, changing the value of a column of a row of CR, or reordering        the rows
ofCR.
 
        If a cursor is open, and the SQL-transaction in which the cursor        was opened makes a significant change
toSQL-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.
        If a holdable cursor is open during an SQL-transaction T and it        is held open for a subsequent
SQL-transaction,then whether any        significant changes made to SQL-data (by T or any subsequent SQL-
transactionin which the cursor is held open) are visible through        that cursor in the subsequent SQL-transaction
beforethat cursor is        closed is determined as follows:
 
        -  If the cursor is insensitive, then significant changes are not           visible.
        -  If the cursor is sensitive, then the visibility of significant           changes is implementation-defined.
        -  If the cursor is asensitive, then the visibility of significant           changes is
implementation-dependent.
        A <declare cursor> DC that specifies WITH RETURN is called a result        set cursor. The <cursor
specification>CR contained in DC defines        a table T; the term result set is used to refer to T. A result set
 cursor, if declared in an SQL-invoked procedure and not closed when        the procedure returns to its invoker,
returnsa result set to the        invoker.
 


--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073