On Wed, 2007-10-24 at 15:50 +0100, Heikki Linnakangas wrote: > Tom Lane wrote: > > "Dharmendra Goyal" <[EMAIL PROTECTED]> writes: > >> If i do update and delete operations on a row pointed by cursor's current > >> then only first operation succeeds, second operation fails. > > > > Hm, by "fails" you mean "does nothing", right? > > > > The reason for this is that WHERE CURRENT OF is implemented as if it > > were WHERE tid = <something>, and historically we've taken that to mean > > the specific tuple at that exact TID. After there's been an update > > already, the tuple at that TID is no longer live to your transaction, > > and so the tid-search fails. To make this work as the spec requires, > > we'd have to be willing to follow the tuple update chain to find the > > currently-live instance of the row. > > > > While I've not tried this, I think we could fix it by having nodeTidscan > > use SnapshotAny instead of the query snapshot when fetching a tuple for > > CurrentOf (but not otherwise, so as to not change the behavior of WHERE > > tid = <something>). We'd essentially be taking it on faith that the > > CurrentOf gave us a TID that was live earlier in the transaction, and > > so is still safe to fetch. I think everything else would just fall out > > if the initial heap_fetch weren't rejecting the tuple. > > > > Comments anyone?
I don't like the faith bit. I'd prefer if we attempted the fetch using the current Snapshot. If that returns an invisible row, then re-fetch at SnapshotAny and follow the chain forwards. That way we're just special casing this situation rather than changing the main line of code. I wonder how serializable transactions are supposed to work in this situation. Can the user really make the transaction throw an error by trying to re-inspect his own changes? Surely not. > That would solve the problem with two updates of the same row, but not this: > UPDATE .. WHERE CURRENT OF... > FETCH RELATIVE 0 Sounds like this problem was a pre-existing issue, but I've not checked. FETCH RELATIVE 0 re-fetches the current row according to the manual. If the current row has been updated then we can only see the new version; the old pre-UPDATE version must not be visible to us, ever. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend