Re: updateable cursors & visibility

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, depending on whether
SENSITIVE, INSENSITIVE, or ASENSITIVE is specified or implied. The
following paragraphs define 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 <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 table associated with the cursor to
be different in 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 row in CR, deleting a row from
CR, changing the value of a column of a row of CR, or reordering
the rows of CR.

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.

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-
transaction in which the cursor is held open) are visible through
that cursor in the subsequent SQL-transaction before that 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, returns a result set to the
invoker.

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