Re: updateable cursors & visibility

Sorry, no idea. Peter's idea is that FOR UPDATE requires SENSITIVE, so
INSENSITIVE has to be READONLY because the update has to see other
changes to be accurate.

I think clearly SENSITIVE/READONLY should be possible, so:

READONLY/SENSITIVE possible
READONLY/INSENSITIVE possible
FOR UPDATE/SENSITIVE possible
FOR UPDATE/INSENSITIVE not possible

READONLY can be either way, while FOR UPDATE requires SENSITIVE.

---------------------------------------------------------------------------

Han wrote:
> So INSENSITIVE => READONLY, but READONLY can not deduce INSENSITIVE, right?
> But in ODBC spec. there's something about cursor like this:
> ------------------------------------------------------------
> If set SQL_ATTR_CURSOR_SENSITIVITY to SQL_SENSITIVE,
> then SQL_ATTR_CONCURRENCY must be SQL_CONCUR_LOCK, SQL_CONCUR_ROWVER, or SQL_CONCUR_VALUES, as specified by the driver. It is never set to SQL_CONCUR_READ_ONLY.
> and SQL_ATTR_CURSOR_TYPE must be SQL_CURSOR_FORWARD_ONLY, SQL_CURSOR_STATIC, SQL_CURSOR_KEYSET_DRIVEN, or SQL_CURSOR_DYNAMIC, as specified by the driver.
> ----------------------------------------------------------------
> And I can not understand why SENSITIVE cursor can not be READONLY. ??? Is this a error made by microsoft?
>
> >Peter Eisentraut wrote:
> >> Neil Conway writes:
> >>
> >> > 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...
> >>
> >> If you allow updatable insensitive cursors, then you are really saying,
> >> whatever happens in the rest of the world does not affect my cursor, but
> >> what I do in my cursor affects the rest of the world. You can easily
> >> construct some cases where this would have bizarre results. For example,
> >> someone inserts a primary key into the underlying table. You don't see it
> >> in the cursor, so you happily insert the same primary key. How can you
> >> and when should you detect this error? Also, since the snapshot of
> >> insensitive cursors is frozen when the cursor is created, would you insert
> >> new rows "in the past"? It's not really workable when you think it
> >> through.
> >
> >You bring up a very good point here I didn't realize --- that when you
> >have a cursor, then do an UPDATE using the cursor information, your
> >UPDATE must have current visibility, not visibility at the time of
> >cursor creation, and I agree with you.
> >
> >I talked to Neil Conway on the phone and we discussed various options.
> >One idea is to require FOR UPDATE on the cursor --- while that prevents
> >other transactions from changing the cursor, it doesn't deal with the
> >current transaction modifying the table outside the cursor. One idea is
> >to have UPDATE/DELETE WHERE CURRENT OF behave like UPDATE/DELETE do now
> >when they find a row that is locked by another transaction --- they wait
> >to see if the transaction commits or aborts, then if committed they
> >follow the tid to the newly updated row, check the WHERE clause to see
> >if it still is satisfied, then perform the update. (Is this correct?)
> >I think WHERE CURRENT OF could do the same thing --- take the tid of the
> >cursor row, find the newest version of the row, wait for any active
> >transaction, re-test the cursor WHERE clause, and update the row. Seems
> >this would make WHERE CURRENT OF behave just like UPDATE, except it is
> >getting its rows from the cursor.
> >
> >As far as someone inserting into the table at the same time, I don't
> >know how to show that row in the cursor, but referential integrity
> >constraints are going to be checked by the UPDATE, and that UPDATE has
> >current visibility, so it should see any inserts that are valid. It
> >doesn't seem much worse than what we have now.
> >
>
> Regards!
>
>
> Han
> zhouhanok(at)vip(dot)sina(dot)com
> 2003-03-26
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

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