Re: [HACKERS] updateable cursors & visibility - Mailing list pgsql-odbc

From Han
Subject Re: [HACKERS] updateable cursors & visibility
Date
Msg-id 20030326052029.0DEC2474E44@postgresql.org
Whole thread Raw
List pgsql-odbc
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.
Itis 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


pgsql-odbc by date:

Previous
From: Antonio Reale
Date:
Subject: MSSQL --> PostgreSQL
Next
From: Bruce Momjian
Date:
Subject: Re: [HACKERS] updateable cursors & visibility