Re: second DML operation fails with updatable cursor

On Wed, 2007-10-24 at 19:47 +0100, Heikki Linnakangas wrote:
> 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.

Neither did I; sorry if I implied that. I searched for any evidence that
other RDBMS supported such a construct and could find nothing.

So hence I say, be strict early, relax later. Otherwise we may have to
support some hazy behaviour for a very long time. Plus I never want to
hear "we can't do feature X because of the need to support scrollable
updateable cursors".

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com