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