Re: second DML operation fails with updatable cursor

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?

That would solve the problem with two updates of the same row, but not this:
UPDATE .. WHERE CURRENT OF...
FETCH RELATIVE 0

At the moment, that returns the next row, not the one that was updated.
Same problem with FETCH NEXT + FETCH PRIOR after the UPDATE.

What does the SQL standard have to say about this?

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com