Re: second DML operation fails with updatable cursor

On Wed, 2007-10-24 at 15:50 +0100, Heikki Linnakangas wrote:
> Tom Lane wrote:
> > "Dharmendra Goyal" <dharmendra(dot)goyal(at)gmail(dot)com> 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