Thread: updateable cursors & visibility
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, and not allowing holdable cursors. However, I'm a little unsure how tuple visibility should work with updateable cursors. In particular: - 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 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? Any comments? Neil
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 Inouehttp://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,
dependingon whether SENSITIVE, INSENSITIVE, or ASENSITIVE is specified or implied. The following
paragraphsdefine 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
<updatestatement: 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
beencommitted before CR was opened, would have caused the table associated with the cursor to be
differentin 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
rowin CR, deleting a row from CR, changing the value of a column of a row of CR, or reordering the rows
ofCR.
If a cursor is open, and the SQL-transaction in which the cursor was opened makes a significant change
toSQL-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-
transactionin which the cursor is held open) are visible through that cursor in the subsequent SQL-transaction
beforethat 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,
returnsa 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,
Pennsylvania19073
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(neworacle.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 //
anduse 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
On Monday 24 March 2003 09:49 pm, 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, and not allowing holdable cursors.
>
> However, I'm a little unsure how tuple visibility should work with
> updateable cursors. In particular:
>
> - 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 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?
>
> Any comments?
>
> Neil
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
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, Pennsylvania19073
So INSENSITIVE => READONLY, but READONLY can not deduce INSENSITIVE, right? But in ODBC spec. there's something about cursor like this: ------------------------------------------------------------ If set SQL_ATTR_CURSOR_SENSITIVITY to SQL_SENSITIVE, then SQL_ATTR_CONCURRENCY must be SQL_CONCUR_LOCK, SQL_CONCUR_ROWVER, or SQL_CONCUR_VALUES, as specified by the driver. Itis never set to SQL_CONCUR_READ_ONLY. and SQL_ATTR_CURSOR_TYPE must be SQL_CURSOR_FORWARD_ONLY, SQL_CURSOR_STATIC, SQL_CURSOR_KEYSET_DRIVEN, or SQL_CURSOR_DYNAMIC,as specified by the driver. ---------------------------------------------------------------- And I can not understand why SENSITIVE cursor can not be READONLY. ??? Is this a error made by microsoft? >Peter Eisentraut wrote: >> Neil Conway writes: >> >> > 11) If an <updatability clause> of FOR UPDATE with or without a <column >> > name list> is specified, then INSENSITIVE shall not be specified and QE >> > shall be updateable. >> > >> > I'm not really sure I see the point of this restriction, though... >> >> If you allow updatable insensitive cursors, then you are really saying, >> whatever happens in the rest of the world does not affect my cursor, but >> what I do in my cursor affects the rest of the world. You can easily >> construct some cases where this would have bizarre results. For example, >> someone inserts a primary key into the underlying table. You don't see it >> in the cursor, so you happily insert the same primary key. How can you >> and when should you detect this error? Also, since the snapshot of >> insensitive cursors is frozen when the cursor is created, would you insert >> new rows "in the past"? It's not really workable when you think it >> through. > >You bring up a very good point here I didn't realize --- that when you >have a cursor, then do an UPDATE using the cursor information, your >UPDATE must have current visibility, not visibility at the time of >cursor creation, and I agree with you. > >I talked to Neil Conway on the phone and we discussed various options. >One idea is to require FOR UPDATE on the cursor --- while that prevents >other transactions from changing the cursor, it doesn't deal with the >current transaction modifying the table outside the cursor. One idea is >to have UPDATE/DELETE WHERE CURRENT OF behave like UPDATE/DELETE do now >when they find a row that is locked by another transaction --- they wait >to see if the transaction commits or aborts, then if committed they >follow the tid to the newly updated row, check the WHERE clause to see >if it still is satisfied, then perform the update. (Is this correct?) >I think WHERE CURRENT OF could do the same thing --- take the tid of the >cursor row, find the newest version of the row, wait for any active >transaction, re-test the cursor WHERE clause, and update the row. Seems >this would make WHERE CURRENT OF behave just like UPDATE, except it is >getting its rows from the cursor. > >As far as someone inserting into the table at the same time, I don't >know how to show that row in the cursor, but referential integrity >constraints are going to be checked by the UPDATE, and that UPDATE has >current visibility, so it should see any inserts that are valid. It >doesn't seem much worse than what we have now. > Regards! Han zhouhanok@vip.sina.com 2003-03-26
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, Pennsylvania19073
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, Pennsylvania19073
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 Inouehttp://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,
Pennsylvania19073
Hiroshi Inoue, But still can't explain this:SENSITIVE => not READ_ONLYIt'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 Inouehttp://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, Pennsylvania19073
> -----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, thenwhether 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
toSQL-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-
transactionin which the cursor is held open) are visible through that cursor in the subsequent SQL-transaction
beforethat 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,
Pennsylvania19073
> -----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, Pennsylvania19073
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 Inouehttp://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 ? > > >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 > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org
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