Re: [HACKERS] updateable cursors visibility
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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] updateable cursors visibility
Gavin Sherry [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] updateable cursors visibility
-Original Message- From: Hannu Krosing [mailto:[EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] updateable cursors visibility
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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] updateable cursors visibility
Hiroshi Inoue kirjutas E, 31.03.2003 kell 19:08: -Original Message- From: Hannu Krosing [mailto:[EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] updateable cursors visibility
Tom Lane wrote: Peter Eisentraut [EMAIL PROTECTED] 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/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] updateable cursors visibility
Peter Eisentraut [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] updateable cursors visibility
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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] updateable cursors visibility
Hiroshi Inoue wrote: -Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED] 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 [EMAIL PROTECTED] | (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 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] updateable cursors visibility
-Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] updateable cursors visibility
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 [EMAIL PROTECTED] | (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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] updateable cursors visibility
-Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] updateable cursors visibility
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 [EMAIL PROTECTED] | (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 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] updateable cursors visibility
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/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] updateable cursors visibility
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 [EMAIL PROTECTED] | (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
Re: [HACKERS] updateable cursors visibility
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/INSENSITIVEpossible FOR UPDATE/SENSITIVEpossible 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 [EMAIL PROTECTED]) Regards! Han [EMAIL PROTECTED] 2003-03-28 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] updateable cursors visibility
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/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] updateable cursors visibility
Bruce Momjian wrote: Sorry, no idea. Peter's idea is that FOR UPDATE requires SENSITIVE, so INSENSITIVE has to be READONLY because the update has to see other changes to be accurate. I think clearly SENSITIVE/READONLY should be possible, so: READONLY/SENSITIVE possible READONLY/INSENSITIVEpossible FOR UPDATE/SENSITIVEpossible 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 [EMAIL PROTECTED])
Re: [HACKERS] updateable cursors visibility
Sorry, no idea. Peter's idea is that FOR UPDATE requires SENSITIVE, so INSENSITIVE has to be READONLY because the update has to see other changes to be accurate. I think clearly SENSITIVE/READONLY should be possible, so: READONLY/SENSITIVE possible READONLY/INSENSITIVEpossible FOR UPDATE/SENSITIVEpossible 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 [EMAIL PROTECTED] 2003-03-26 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] updateable cursors visibility
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; iresultset_types.length; i++)
{
for (int j=0; jconcurrency_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 onlyConcurrency: 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 onlyConcurrency: Updatable
supportResultSetType: true
supportsResultSetConcurrency: true
ownUpdatesAreVisible: false
ownDeletesAreVisible: false
[HACKERS] 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] updateable cursors visibility
Neil Conway [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] updateable cursors visibility
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 [EMAIL PROTECTED] | (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 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] updateable cursors visibility
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/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] updateable cursors visibility
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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
