Re: second DML operation fails with updatable cursor

Tom Lane wrote:
> Simon Riggs <simon(at)2ndquadrant(dot)com> writes:
>>> Tom Lane wrote:
>>>> 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.
>
>> I don't like the faith bit.
>
> Well, don't worry, because it doesn't work anyway. What does seem to
> work properly is applying heap_get_latest_tid() to the scan TID obtained
> from the cursor.

An interesting point from the (draft version of) SQL:2003:

After a DELETE WHERE CURRENT OF, the cursor position is "before the next
row". An UPDATE WHERE CURRENT OF is supposed raise an exception
condition, if the cursor is not positioned on a row. So DELETE WHERE
CURRENT OF followed by an UPDATE WHERE CURRENT OF is supposed to throw
an error.

Another interesting point, from the General Rules section of UPDATE
WHERE CURRENT OF:

"If, while CR is open, an object row has been marked for deletion by any
<delete statement: searched>,
marked for deletion by any <delete statement: positioned> that
identifies any cursor other than CR, updated
by any <update statement: searched>, updated by any <update statement:
positioned>, or updated by any
<merge statement> that identifies any cursor other than CR, then a
completion condition is raised: warning
— cursor operation conflict."

I don't think it's a big deal if we don't implement those errors and
warnings, though.

>> FETCH RELATIVE 0 re-fetches the current row according to the manual.
>
> The question is what's the current row, remembering that we've always
> defined our cursors as INSENSITIVE.

I tried to find an answer to that in the spec. I'm pretty you're
supposed to see the changes of UPDATEs done through the same cursor,
using WHERE CURRENT OF, even with insensitive cursors. I have no idea
how we could implement that, though.

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