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

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@vip.sina.com
> ��������������������2003-03-26
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>

--
  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, Pennsylvania 19073