Re: Questions about update, delete, ctid...

On Mon, Jul 31, 2006 at 01:47:41AM +0200, Florian G. Pflug wrote:
> I agree, at least for "for-update"-cursors. If the cursor was not
> declared "for update", then it is not even cleaer to me what the
> correct behaviour would be. Imagine that you declared a cursor, and fetched
> a row. After fetching, but before you call "update table foo where current
> of
> mycursor" someone updates the row (and commits), and the new version would
> have
> never been returned by your select statement in the first place. Should
> that row be
> updates, or not? What if the other transaction deleted the row - should your
> update raise an error? fail silently?

That's what the EPQ machinery that Tom talked about does. It will trace
a newer version, raise a serialization failure or do nothing as
appropriate. I really don't think you need to do anything special in
this case.

Read the src/backend/executor/README file for details.

> Because of those problems, I'll always assume that the cursor was declared
> "for update" from now on.
>
> After reading backend code for the last hours, I've created the following
> plan for
> implementing "where current of"

<snip>

Looks like a good plan, but I think you've overlooked something: if
you've locked the tuple FOR UPDATE then by definition there cannot be a
newer version, right? (Incidently, I see message about FOR UPDATE not
being supported on cursors).

More specifically, if you have a tuple locked FOR UPDATE and there is a
newer version, that almost certaintly means this new version is dead,
part of an aborted transaction. Comments from more knowledgable people
would be good though. Locked tuples are usually easier.

I think plan A is the way to go. Good luck.

Hope this helps,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.