Richard Huxton wrote:
Richard Huxton wrote:
Heikki Linnakangas wrote:
The problem is that the new tuple version is checked only against the
condition in the update rule, id=OLD.id, but not the condition in the
original update-claus, dt='a'.
Yeah, that's confusing :(.
Bit more than just normal rule confusion I'd say. Try the following
two statements in parallel (assuming you've just run the previous):
UPDATE test SET dt='c';
UPDATE test SET dt='x' FROM test t2 WHERE test.id=t2.id AND t2.dt='b';
This isn't a problem with the view mechanism - it's a problem with
re-checking clauses involving subqueries or joins I'd guess.
I'm trying to decide if it's unexpected or just plain wrong, and I
think I'd have to argue wrong.
Or perhaps I'd not argue that :-/
Well, src/backend/executor/README agrees with you that it's wrong..
"Note a fundamental bogosity of this approach: if the relation containing
the original tuple is being used in a self-join, the other instance(s) of
the relation will be treated as still containing the original tuple, whereas
logical consistency would demand that the modified tuple appear in them too.
But we'd have to actually substitute the modified tuple for the original,
while still returning all the rest of the relation, to ensure consistent
answers. Implementing this correctly is a task for future work."
This is really about MVCC in read committed mode, and the "just right
for simpler cases":
http://www.postgresql.org/docs/8.2/interactive/transaction-iso.html#XACT-READ-COMMITTED
Clearly there needs to be a change to the sentence: "Because of the
above rule, it is possible for an updating command to see an
inconsistent snapshot: it can see the effects of concurrent updating
commands that affected the same rows it is trying to update"
Not true if there's a subquery/join involved.
If the cited part of the README is correct, then all joins and subqueries
are fine, except if they refer to the table being updated.
I think there should be a big, fat warning that self-referential
updates have highly non-obvious behaviour in read-committed mode,
and should be avoided.
greetings, Florian Pflug
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
