Re: second DML operation fails with updatable cursor

Tom Lane wrote:
> Heikki Linnakangas <heikki(at)enterprisedb(dot)com> writes:
>> Yes, re-fetching row you just deleted is supposed to raise an error.
>> That doesn't seem very hard to implement. If an UPDATE/DELETE CURRENT OF
>> doesn't find the tuple to update/delete, raise an error.
>
> Uh, no, the error would have to come from FETCH RELATIVE 0, and there's
> a problem because no single piece of the code has all the facts needed
> to know that an error should be thrown. I don't currently see any
> non-klugy way to detect that.

No, FETCH RELATIVE 0 is supposed to be a no-op. If the cursor is
positioned "before a row", it's still positioned before a row after
FETCH RELATIVE 0. That's the way I read the spec, anyway.

But if it's not easy to do, I'm OK with leaving that out.

> It might make sense to go with Simon's suggestion to just forbid
> non-forwards fetch from a FOR UPDATE cursor (assuming that we agree he's
> read the spec correctly to disallow that).

I don't see that in the spec.

It does say that "if <updatability clause> is not specified, then if
either INSENSITIVE, SCROLL, or ORDER BY is specified, or if QE is not a
simply updatable table, then an <updatability clause> of READ ONLY is
implicit". It also says "If an <updatability clause> of FOR UPDATE with
or without a <column name list> is specified, then INSENSITIVE shall not
be specified". But I don't see anything forbidding SCROLL FOR UPDATE
combination.

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