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


Wed, 26 Mar 2003 13:27:23 +0800
"Han" <zhouhanok@vip.sina.com>
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@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


Wed, 26 Mar 2003 11:01:26 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: updateable cursors & visibility
Bruce Momjian wrote:
> 
> 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.

SENSITIVE doesn't mean *not INSENESITIVE*.
INSENSITIVE doesn't mean *not SENSITIVE*.

regards,
Hiroshi Inoue
	http://www.geocities.jp/inocchichichi/psqlodbc/


Thu, 27 Mar 2003 18:06:39 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: updateable cursors & visibility