Re: Concurrently updating an updatable view

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