Martijn van Oosterhout wrote:
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.
I can see how the EPQ machinery can be used to chain forward to the
correct row to be updated, even if I originally found an older version
(e.g. by searching for a specific ctid). But for non-"for
update"-cursors, the newest version of the row returned by fetch could
be modified such that it would have never been returned by fetch in the
first place. Image two transactions A and B:
A: begin
B: begin
A: declare c_foo cursor for select * from foo where type='A';
A: fetch c_foo -- the returned row is *not* locked.
B: update foo set type='B' -- There are no rows with type='B' now
B: commit
A: update foo set type='A' where current of c_foo
The EPQ machinery can now guarantee that we actually try to update
the correct row version. But I'm unsure if the last update should
do anything at all. Because at the time of the update, the current
row of the cursor c_foo wouldn't haven been returned by fetch, because
it's type is now 'B' and not 'A'.
This is why I believe that the use of "current of" to cursors that take
rowlevel share locks in the right thing to do.
Read the src/backend/executor/README file for details.
Done that ;-)
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).
There can be, because the transaction owning the lock could have updated
the row itself.
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.
Sounds cleaner to me too, even though it extends the spec a bit.
greetings, Florian Pflug
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
