Re: second DML operation fails with updatable cursor

"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?

regards, tom lane