updateable cursors & visibility

Neil Conway <neilc@samurai.com> writes:

- if the user updates a row X in the cursor, then rewinds the cursor and
fetches X again, should they see the new X or the old X?

If it's considered an insensitive cursor, I'd think it should see the
old X. You would have a hard time making the code do otherwise in any
case --- the cursor's snapshot is frozen when the cursor is created.

- if the user updates a row X in the cursor, and then a query within the
cursor's transaction views X, should the query see new X or old X?

The subsequent query (if it's not going through the cursor) should and
will see the new X.

This should all be handled for you by the command-counter-based
visibility rules; I don't think you need to worry about it.

regards, tom lane

Neil Conway wrote:

Folks,

I'd like to implement updateable cursors. I'll be working on just
getting updateable cursors working for relatively simple SELECT queries
(e.g. no joins, aggregates, grouping, user-defined function calls,
etc.). BTW, I believe that's all the SQL spec requires, but I need to
double check that. I'm also planning on only implementing only
INSENSITIVE cursors,

Does the SQL standard allow INSENSITIVE updatable cursors ?

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

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

Cheers,

Neil

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

Hello Neil,
I try example for Oracle jdbc 1.4 driver
This is sample and results.
I hope that is helps.

If you want yet another example, please tell me.

regards
Haris Peco

/**
* A simple sample to check the availability of scrollable result sets.
*
* Please use jdk1.2 or later version
*/

import java.sql.*;

public class ResultSetmy1
{
public static void main(String[] args) throws SQLException
{
// Load the Oracle JDBC driver
DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

String url = "jdbc:oracle:thin:@spnew:1521:V9i";
try {
String url1 = System.getProperty("JDBC_URL");
if (url1 != null)
url = url1;
} catch (Exception e) {
// If there is any security exception, ignore it
// and use the default
}

// Connect to the database
// You can put a database name after the @ sign in the connection URL.
Connection conn =
DriverManager.getConnection (url, "scott", "tiger");

// Get the metadata regarding this connection's database
DatabaseMetaData dbmd = conn.getMetaData();

// List all the possible result set types
int resultset_types[] =
{
ResultSet.TYPE_FORWARD_ONLY,
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.TYPE_SCROLL_SENSITIVE
};

// List all the possible result set concurrency types
int concurrency_types[] =
{
ResultSet.CONCUR_READ_ONLY,
ResultSet.CONCUR_UPDATABLE
};

// List the result set type names
String resultset_types_msg [] =
{
"Forward only",
"Scroll insensitive",
"Scroll sensitive"
};

// List the concurrency type names
String concurrency_types_msg[] =
{
"Read only",
"Updatable"
};

// Check the availability of the result type and concurrency type
for (int i=0; i<resultset_types.length; i++)
{
for (int j=0; j<concurrency_types.length; j++)
{
int type = resultset_types[i];
int concurrency = concurrency_types[j];

System.out.println ("Type: "+resultset_types_msg[i]+" "+
"Concurrency: "+concurrency_types_msg[j]);
System.out.println
("----------------------------------------------------");

// Return true if the result set type is supported
System.out.println ("supportResultSetType: "+
dbmd.supportsResultSetType(type));

// Return true if the result set type and concurrency type is supported
System.out.println ("supportsResultSetConcurrency: "+
dbmd.supportsResultSetConcurrency(type, concurrency));

// Return true if the result set's updates are visible
System.out.println ("ownUpdatesAreVisible: "+
dbmd.ownUpdatesAreVisible(type));

// Return true if the result set's deletions are visible
System.out.println ("ownDeletesAreVisible: "+
dbmd.ownDeletesAreVisible(type));

// Return true if the result set's insertions are visible
System.out.println ("ownInsertAreVisible: "+
dbmd.ownInsertsAreVisible(type));

// Return true if other's changes are visible
System.out.println ("othersUpdatesAreVisible: "+
dbmd.othersUpdatesAreVisible(type));

// Return true if other's deletions are visible
System.out.println ("othersDeletesAreVisible: "+
dbmd.othersDeletesAreVisible(type));

// Return true if other's insertions are visible
System.out.println ("othersInsertsAreVisible: "+
dbmd.othersInsertsAreVisible(type));

// Return true if ResultSet.rowUpdated() is supported
System.out.println ("updatesAreDetected: "+
dbmd.updatesAreDetected(type));

// Return true if ResultSet.rowDeleted() is supported
System.out.println ("deletesAreDetected: "+
dbmd.deletesAreDetected(type));

// Return true if ResultSet.rowInserted() is supported
System.out.println ("insertsAreDetected: "+
dbmd.insertsAreDetected(type));

System.out.println ();
}
}

// Close the connection
conn.close();
}
}

This is results :

Type: Forward only Concurrency: Read only
----------------------------------------------------
supportResultSetType: true
supportsResultSetConcurrency: true
ownUpdatesAreVisible: false
ownDeletesAreVisible: false
ownInsertAreVisible: false
othersUpdatesAreVisible: false
othersDeletesAreVisible: false
othersInsertsAreVisible: false
updatesAreDetected: false
deletesAreDetected: false
insertsAreDetected: false

Type: Forward only Concurrency: Updatable
----------------------------------------------------
supportResultSetType: true
supportsResultSetConcurrency: true
ownUpdatesAreVisible: false
ownDeletesAreVisible: false
ownInsertAreVisible: false
othersUpdatesAreVisible: false
othersDeletesAreVisible: false
othersInsertsAreVisible: false
updatesAreDetected: false
deletesAreDetected: false
insertsAreDetected: false

Type: Scroll insensitive Concurrency: Read only
----------------------------------------------------
supportResultSetType: true
supportsResultSetConcurrency: true
ownUpdatesAreVisible: true
ownDeletesAreVisible: true
ownInsertAreVisible: false
othersUpdatesAreVisible: false
othersDeletesAreVisible: false
othersInsertsAreVisible: false
updatesAreDetected: false
deletesAreDetected: false
insertsAreDetected: false

Type: Scroll insensitive Concurrency: Updatable
----------------------------------------------------
supportResultSetType: true
supportsResultSetConcurrency: true
ownUpdatesAreVisible: true
ownDeletesAreVisible: true
ownInsertAreVisible: false
othersUpdatesAreVisible: false
othersDeletesAreVisible: false
othersInsertsAreVisible: false
updatesAreDetected: false
deletesAreDetected: false
insertsAreDetected: false

Type: Scroll sensitive Concurrency: Read only
----------------------------------------------------
supportResultSetType: true
supportsResultSetConcurrency: true
ownUpdatesAreVisible: true
ownDeletesAreVisible: true
ownInsertAreVisible: false
othersUpdatesAreVisible: true
othersDeletesAreVisible: false
othersInsertsAreVisible: false
updatesAreDetected: false
deletesAreDetected: false
insertsAreDetected: false

Type: Scroll sensitive Concurrency: Updatable
----------------------------------------------------
supportResultSetType: true
supportsResultSetConcurrency: true
ownUpdatesAreVisible: true
ownDeletesAreVisible: true
ownInsertAreVisible: false
othersUpdatesAreVisible: true
othersDeletesAreVisible: false
othersInsertsAreVisible: false
updatesAreDetected: false
deletesAreDetected: false
insertsAreDetected: false

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.

--
Peter Eisentraut peter_e@gmx.net

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.

Comments?

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

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

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

OK, no one has replied to this proposal, so I assume the approach is
good.

As far as implementation, I assume we add a field to the cursor
structure to record the most recently fetched tid (if multiple rows are
fetched, it is the last row).

Now, when UPDATE/DELETE ... WHERE CURRENT OF, make sure the table
mentioned is the same as the single table in the cursor. Then, use the
tid to find the most recent version of that row. heap_update seems to
be where this all happens. Also, make sure you check the cursor WHERE
condition before doing the update.

Basically, this allows us to get rows from the INSENSITIVE cursor, but
have the UPDATE have current visibility.

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

Bruce Momjian wrote:

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.

Comments?

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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

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

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/

Bruce Momjian writes:

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.

That would only keep existing rows from being deleted but not new rows
from being added.

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

Surely it would have to do something like that, but that's a matter of the
transaction isolation, not the sensitivity. It doesn't do anything to
address the potential problems I mentioned.

--
Peter Eisentraut peter_e@gmx.net

Peter Eisentraut wrote:

Bruce Momjian writes:

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.

That would only keep existing rows from being deleted but not new rows
from being added.

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

Surely it would have to do something like that, but that's a matter of the
transaction isolation, not the sensitivity. It doesn't do anything to
address the potential problems I mentioned.

Well, a unique constraint on the row would see your other INSERT. I
don't see how making an INSERT visible in the cursor would help us, and
I don't see how we would implement that except by rerunning the query
for each fetch, which seems like a bad idea.

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

Bruce Momjian wrote:

Peter Eisentraut wrote:

Bruce Momjian writes:

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.

That would only keep existing rows from being deleted but not new rows
from being added.

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

Surely it would have to do something like that, but that's a matter of the
transaction isolation, not the sensitivity. It doesn't do anything to
address the potential problems I mentioned.

Well, a unique constraint on the row would see your other INSERT. I
don't see how making an INSERT visible in the cursor would help us, and
I don't see how we would implement that except by rerunning the query
for each fetch, which seems like a bad idea.

I don't understand what you two are discussing.
What's is SENSITIVE, INSENSITIVE or ASESNSITIVE ?

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

Hiroshi Inoue wrote:

Bruce Momjian wrote:

Peter Eisentraut wrote:

Bruce Momjian writes:

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.

That would only keep existing rows from being deleted but not new rows
from being added.

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

Surely it would have to do something like that, but that's a matter of the
transaction isolation, not the sensitivity. It doesn't do anything to
address the potential problems I mentioned.

Well, a unique constraint on the row would see your other INSERT. I
don't see how making an INSERT visible in the cursor would help us, and
I don't see how we would implement that except by rerunning the query
for each fetch, which seems like a bad idea.

I don't understand what you two are discussing.
What's is SENSITIVE, INSENSITIVE or ASESNSITIVE ?

In SQL99 standard, I see:

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

So, I think we have two issues --- what does the cursor see, and what
does the UPDATE see. I think we have to have the cursor remain
INSENSITIVE, because we don't at fetch time whether WHERE CURRENT OF is
going to be used. One nice thing is that while the standard says you
can't specify INSENSITIVE for a WHERE CURRENT OF cursor, we can say it
is ASENSITIVE and that will match our behavior. (We just need a boolean
to make sure if they do specify INSENSTIVIVE that WHERE CURRENT OF
throws an error.)

Then, when we do the UPDATE, the UPDATE is SENSITIVE in that it sees the
most recent version of the tuple, assuming the newest tuple still
matches the WHERE clause of the cursor. The UPDATE also has to do
contraint checking using current visibility.

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

Hiroshi Inoue,

But still can't explain this:
SENSITIVE => not READ_ONLY
It's in the ODBC Spec.

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/

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Regards!

　　　　　　　　Han
　　　　　　　　zhouhanok@vip.sina.com
　　　　　　　　　　2003-03-28

Bruce Momjian wrote:

Hiroshi Inoue wrote:

Bruce Momjian wrote:

Peter Eisentraut wrote:

Bruce Momjian writes:

I don't understand what you two are discussing.
What's is SENSITIVE, INSENSITIVE or ASESNSITIVE ?

In SQL99 standard, I see:

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

While a cursor is open, another application inserted a
row which satisfies the condition to be contained in
the cursor and committed. Then
If the cursor is SENSITIVE, must it see the row ?
If the cursor is INSENSITIVE, it mustn't see the row ?

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

Hiroshi Inoue wrote:

I don't understand what you two are discussing.
What's is SENSITIVE, INSENSITIVE or ASESNSITIVE ?

In SQL99 standard, I see:

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

While a cursor is open, another application inserted a
row which satisfies the condition to be contained in
the cursor and committed. Then
If the cursor is SENSITIVE, must it see the row ?

Right.

If the cursor is INSENSITIVE, it mustn't see the row ?

Right.

What I don't understand is how the row would automatically appear in a
sensitive cursor. If the cursor has an ORDER BY, and there are 100
rows, and I am on row 50, and someone inserts a row that is ordered just
before 50, is my current row now 51, and 50 is the new added row, and if
I do a MOVE -1, I see the most recently inserted row and not the row I
saw as 49 before. Seems quite confusing.

Anyway, my idea is to have the existing cursor rows appear as updated to
the UPDATE, but to remain insensitive in the cursor itself. We could
change that, but it would require us to somehow mark the cursor at
declare time to indicate that it will be used with WHERE CURRENT OF.

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

-----Original Message-----
From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]

Hiroshi Inoue wrote:

I don't understand what you two are discussing.
What's is SENSITIVE, INSENSITIVE or ASESNSITIVE ?

In SQL99 standard, I see:

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

While a cursor is open, another application inserted a
row which satisfies the condition to be contained in
the cursor and committed. Then
If the cursor is SENSITIVE, must it see the row ?

Right.

If the cursor is INSENSITIVE, it mustn't see the row ?

Right.

If so, isn't the difference between SENSITIVE and INSENSITIVE extreme ?
Why do you or Peter refer to ASENSITIVE little ?
And what does the following mean ? It is placed just before the sentences
you quoted first.

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:

regards,
Hiroshi Inoue