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

Hiroshi Inoue wrote:

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

Right.

If so, isn't the difference between SENSITIVE and INSENSITIVE extreme ?

Yes.

Why do you or Peter refer to ASENSITIVE little ?

Not sure --- ASENSITIVE seems to be "do whatever you want", which is
always good.

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:

There seem to be two sections, one dealing with seeing change made by
the same transaction:

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:

and then another talking about holdable cursors after the cursor
transaction completes:

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:

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

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

Right.

If so, isn't the difference between SENSITIVE and INSENSITIVE extreme ?

Yes.

Why do you or Peter refer to ASENSITIVE little ?

Not sure --- ASENSITIVE seems to be "do whatever you want", which is
always good.

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:

There seem to be two sections, one dealing with seeing change made by
the same transaction:

May I ask again ?
Must a SENSITIVE cursor see other applications' changes made
while the cursor is open ?

regards,
Hiroshi Inoue

Hiroshi Inoue wrote:

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

Hiroshi Inoue wrote:

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

Right.

If so, isn't the difference between SENSITIVE and INSENSITIVE extreme ?

Yes.

Why do you or Peter refer to ASENSITIVE little ?

Not sure --- ASENSITIVE seems to be "do whatever you want", which is
always good.

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:

There seem to be two sections, one dealing with seeing change made by
the same transaction:

May I ask again ?
Must a SENSITIVE cursor see other applications' changes made
while the cursor is open ?

I think I see what you are saying, that the spec talks about seeing your
own transactions changes, and about seeing things after your transaction
commits, but doesn't really talk about seeing other transactions changes
while you are in the cursor's transaction. Is that the point?

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

Must a SENSITIVE cursor see other applications' changes made
while the cursor is open ?

Yes. It is immaterial whether the change came from a different
application or the same one.

Nevertheless, the cursor sensitivity does not excuse you from observing
the transaction isolation level. So even if the cursor is sensitive you
should not be able to see other transactions' changes if you are in a
serializable transaction.

--
Peter Eisentraut peter_e@gmx.net

-----Original Message-----
From: Peter Eisentraut [mailto:peter_e@gmx.net]

Hiroshi Inoue writes:

Must a SENSITIVE cursor see other applications' changes made
while the cursor is open ?

Yes. It is immaterial whether the change came from a different
application or the same one.

If I recognize the standard correctly, SENSITIVE cursors don't
have to see the changes of other transactions.

regards,
Hiroshi Inoue

Peter Eisentraut <peter_e@gmx.net> writes:

Hiroshi Inoue writes:

Must a SENSITIVE cursor see other applications' changes made
while the cursor is open ?

Yes. It is immaterial whether the change came from a different
application or the same one.
Nevertheless, the cursor sensitivity does not excuse you from observing
the transaction isolation level. So even if the cursor is sensitive you
should not be able to see other transactions' changes if you are in a
serializable transaction.

Serializable or not, there is a good case for saying that cursors don't
see changes made after they are opened, period. The current
implementation locks down the cursor's snapshot at DECLARE time. If we
allow the snapshot to change later, what in the world will the semantics
be? Will we go back to re-scan rows that we previously skipped? I do
not think we have a prayer of making consistent, predictable behavior
that works any other way.

regards, tom lane

Tom Lane wrote:

Peter Eisentraut <peter_e@gmx.net> writes:

Hiroshi Inoue writes:

Must a SENSITIVE cursor see other applications' changes made
while the cursor is open ?

Yes. It is immaterial whether the change came from a different
application or the same one.
Nevertheless, the cursor sensitivity does not excuse you from observing
the transaction isolation level. So even if the cursor is sensitive you
should not be able to see other transactions' changes if you are in a
serializable transaction.

Serializable or not, there is a good case for saying that cursors don't
see changes made after they are opened, period. The current
implementation locks down the cursor's snapshot at DECLARE time.

It's only because PostgreSQL( or other MVCC based DBMS) is
easy and natural to implement cursors in such a way. However,
It isn't a requirement of the SQL standard, IIRC.

As for ODBC, ODBC has the following cursor types about the
visibility of other changes.
1) static
It never detects other changes.
2) dynamic
It can detect any changes made to the membership, order,
and values of the result set after the cursor is opened.
3) keyset-driven
It always detects changes to the values of rows.
4) mixed
A combination of a keyset-driven cursor and a dynamic
cursor.

It's not clear to me now how we should realize the above
type of cursors at server side.

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

Hiroshi Inoue kirjutas E, 31.03.2003 kell 03:40:

Tom Lane wrote:

Serializable or not, there is a good case for saying that cursors don't
see changes made after they are opened, period. The current
implementation locks down the cursor's snapshot at DECLARE time.

It's only because PostgreSQL( or other MVCC based DBMS) is
easy and natural to implement cursors in such a way. However,
It isn't a requirement of the SQL standard, IIRC.

As for ODBC, ODBC has the following cursor types about the
visibility of other changes.
1) static
It never detects other changes.

This seems the clearest ?

2) dynamic
It can detect any changes made to the membership, order,
and values of the result set after the cursor is opened.

What would it mean in practice, i.e. if you are on the first row in the
cursor and then update tha ORDER BY field so that your row becomes the
last one, will the next FETCH move the cursor past end ?

what happens, if the row you are on is removed from the keyset, either
by current or any other backend ?

What about the case when you have moved past the last row, and suddenly
a new row appears which is positioned after the last row ?

What about when you are moving over several rows that have the same
ordering position and then one more appears - should it go before or
after the current position ?

3) keyset-driven
It always detects changes to the values of rows.

What about _new_ rows, or rows that no more belong to the "keyset" ?

4) mixed
A combination of a keyset-driven cursor and a dynamic
cursor.

Combined in what way ?

It's not clear to me now how we should realize the above
type of cursors at server side.

From your short description it is not even clear for me how *exactly*

should they behave.

------------------
Hannu

-----Original Message-----
From: Hannu Krosing [mailto:hannu@tm.ee]

Hiroshi Inoue kirjutas E, 31.03.2003 kell 03:40:

2) dynamic
It can detect any changes made to the membership, order,
and values of the result set after the cursor is opened.

What would it mean in practice, i.e. if you are on the first
row in the
cursor and then update tha ORDER BY field so that your row becomes the
last one, will the next FETCH move the cursor past end ?

No. The row next to the old first row would be FETCHed.

what happens, if the row you are on is removed from the keyset, either
by current or any other backend ?

The dynamic cursor doesn't see the row any longer.

What about the case when you have moved past the last row,
and suddenly
a new row appears which is positioned after the last row ?

The dynamic cursor would see the new row when it is fetched backwards.

What about when you are moving over several rows that have the same
ordering position and then one more appears - should it go before or
after the current position ?

It is implementation dependent.

3) keyset-driven
It always detects changes to the values of rows.

What about _new_ rows,

It never detects new rows.

or rows that no more belong to the "keyset" ?

They are the same as deleted ones.

4) mixed
A combination of a keyset-driven cursor and a dynamic
cursor.

Combined in what way ?

It uses a limited keyset size. If a fetch operation doesn't fit in
the range of the keyset, it dynamically fetches the result set.

It's not clear to me now how we should realize the above
type of cursors at server side.

From your short description it is not even clear for me how *exactly*

should they behave.

I only intended to illustrate various type of visibilities roughly
because
there were no such reference in this thread.

regards,
Hiroshi Inoue

Hiroshi Inoue kirjutas E, 31.03.2003 kell 19:08:

-----Original Message-----
From: Hannu Krosing [mailto:hannu@tm.ee]

Hiroshi Inoue kirjutas E, 31.03.2003 kell 03:40:

2) dynamic
It can detect any changes made to the membership, order,
and values of the result set after the cursor is opened.

What would it mean in practice, i.e. if you are on the first
row in the
cursor and then update tha ORDER BY field so that your row becomes the
last one, will the next FETCH move the cursor past end ?

No. The row next to the old first row would be FETCHed.

In what way would the _changes_made_to_the_order_ be reflected then ?

what happens, if the row you are on is removed from the keyset, either
by current or any other backend ?

The dynamic cursor doesn't see the row any longer.

It seems to be doable with MVCC - "just" ;) check for visibility of
underlying tuples at each fetch. At least it does not seem any harder
for MVCC than for other CC methods.

3) keyset-driven
It always detects changes to the values of rows.

What about _new_ rows,

It never detects new rows.

Then I must have misunderstood the "can detect any changes made to the
membership, order, and values" part. I assumed that "any changes" wold
also include rows that magically become part of the query by either
changes in values or being inserted.

or rows that no more belong to the "keyset" ?

They are the same as deleted ones.

So they are no more visible to cursor ?

Tom Lane writes:

Serializable or not, there is a good case for saying that cursors don't
see changes made after they are opened, period.

No one disputes that insensitive cursors are a valid concept. But this
discussion is about updating such a cursor. What view of the data would
such a cursor represent after an update? What about this example:

CREATE TABLE foo (a int PRIMARY KEY);
INSERT INTO foo VALUES (1);
... much later ...
BEGIN;
DECLARE test INSENSITIVE CURSOR FOR UPDATE FOR SELECT a FROM foo;
INSERT INTO foo VALUES (2);
FETCH NEXT FROM test;
UPDATE foo SET a = 2 WHERE CURRENT OF test;
...
COMMIT;

Does the UPDATE catch the constraint violation?

If we allow the snapshot to change later, what in the world will the
semantics be? Will we go back to re-scan rows that we previously
skipped?

Clearly this issue is yucky, that's why they probably invented asensitive
cursors. I wouldn't be surprised to learn that there was some existing
implementation that had updatable insensitive cursors, but the working
group decided it wasn't sound and invented a separate half-way type for
it.

--
Peter Eisentraut peter_e@gmx.net

-----Original Message-----
From: Hannu Krosing [mailto:hannu@tm.ee]

Hiroshi Inoue kirjutas E, 31.03.2003 kell 19:08:

-----Original Message-----
From: Hannu Krosing [mailto:hannu@tm.ee]

Hiroshi Inoue kirjutas E, 31.03.2003 kell 03:40:

2) dynamic
It can detect any changes made to the membership, order,
and values of the result set after the cursor is opened.

What would it mean in practice, i.e. if you are on the first
row in the
cursor and then update tha ORDER BY field so that your

row becomes the

last one, will the next FETCH move the cursor past end ?

No. The row next to the old first row would be FETCHed.

In what way would the _changes_made_to_the_order_ be reflected then ?

Once a rowset is fetched, the membership or order in the rowset is
fixed until the rowset is invalidated by another fetch operation and
the fetch operation is executed for the rowset.

what happens, if the row you are on is removed from the

keyset, either

by current or any other backend ?

The dynamic cursor doesn't see the row any longer.

It seems to be doable with MVCC - "just" ;) check for visibility of
underlying tuples at each fetch. At least it does not seem any harder
for MVCC than for other CC methods.

MVCC has no particular advantage at this point anyway.

3) keyset-driven
It always detects changes to the values of rows.

What about _new_ rows,

It never detects new rows.

Then I must have misunderstood the "can detect any changes made to the
membership, order, and values" part. I assumed that "any changes" wold
also include rows that magically become part of the query by either
changes in values or being inserted.

It's a spec of dynamic cursors not of keyset-driven ones.

or rows that no more belong to the "keyset" ?

They are the same as deleted ones.

So they are no more visible to cursor ?

Yes.

regards,
Hiroshi Inoue

On Mon, 31 Mar 2003, Peter Eisentraut wrote:

Tom Lane writes:

Serializable or not, there is a good case for saying that cursors don't
see changes made after they are opened, period.

No one disputes that insensitive cursors are a valid concept. But this
discussion is about updating such a cursor. What view of the data would
such a cursor represent after an update? What about this example:

CREATE TABLE foo (a int PRIMARY KEY);
INSERT INTO foo VALUES (1);
... much later ...
BEGIN;
DECLARE test INSENSITIVE CURSOR FOR UPDATE FOR SELECT a FROM foo;
INSERT INTO foo VALUES (2);
FETCH NEXT FROM test;
UPDATE foo SET a = 2 WHERE CURRENT OF test;
...
COMMIT;

Does the UPDATE catch the constraint violation?

Good point. There is no direct reference to this condition in SQL99 -- as
far as I can tell. We do have this however in SQL99 14.9 General Rules:

4) The extent to which an SQL-implementation may disallow independent
changes that are not significant is implementation-defined

Where 'independent' means a change not made by <update
statement: positioned> or <delete statement: positioned> and 'significant'
means that, had the change been made before the cursor was opened, the
underlying table of the cursor would be different in some respect. If the
cursor is insensitive, then significant changes are not visible by the
cursor.

So, for insensitive cursors we could disallow changes independent of the
cursor. I don't think this is useful but it suggests that sensitive
cursors should allow independent changes and that these should be visible
to the cursor. But that isn't really the issue.

We also get the following in SQL99 <update statement: positioned> General
Rules:

8) If, while CR is open, an object row has been marked for deletion by any
<delete statement: searched>, marked for deletion by any <delete
statement: positioned> that identifies any cursor other than CR, updated
by any <update statement: searched>, or updated by any <update
statement: positioned> that identifies any cursor other than CR, then a
completion condition is raised: warning cursor operation conflict.

But this just muddies the waters. I think it comes down to
this: insensitive cursors should behave as they currently do. If they are
used by update/delete statement: positioned, they still need to adhere to
the normal visibility of UPDATE or DELETE -- which is what, I think, Bruce
originally proposed.

So, I would like to go ahead and implement update/delete
statement: positioned (regardless of the nature of visibility we decide
on). Bruce's proposal basically sees the tid of the last FETCH'd or MOVE'd
to tuple stored in the Portal structure for that cursor. This shouldn't be
hard since DoRelativeFetch() calls ExecutorRun() for non-holdable cursors,
which returns the last TupleTableSlot returned, which gives access to the
tid of the last FETCH'd tuple. Likewise, DoRelativeStoreFetch() seems to
have direct access to the data required, covering holdable cursors.

This means that when we handle an update/delete statement: positioned, we
could either do a rough hack and look up the Portal for the named cursor
inside of the parse and analyze code and fill out a where clause Node to
the effect of 'ctid = <saved tid>'.

Alternatively, we could just register that it is a positioned update/delete
and look it up somewhere else: planner, executor...?

Regardless of which, we could insert a special case in ExecutePlan() (or
somewhere more appropriate?) to test that the tuple returned from the
lower level ExecTidScan() still satisifies the cursor query. It should be
sufficient to use HeapTupleSatisfies() or some of the logic there in to do
this. If all goes well, then the update/delete will succeed.

Comments?

Gavin

Gavin Sherry <swm@linuxworld.com.au> writes:

Regardless of which, we could insert a special case in ExecutePlan() (or
somewhere more appropriate?) to test that the tuple returned from the
lower level ExecTidScan() still satisifies the cursor query. It should be
sufficient to use HeapTupleSatisfies() or some of the logic there in to do
this.

I don't think so; you'll need to use EvalPlanQual. This is not
different from the situation where a regular UPDATE finds a tuple that is
not the latest version of its row.

Should I point out that we already have semantics for that behavior, and
they depend on the serialization mode? Most of this discussion seems to
completely ignore the MVCC semantics we already have for updates/deletes
applied to rows that aren't current anymore. ISTM we should stick to
that behavior ...

regards, tom lane