Re: Concurrently updating an updatable view

Florian G. Pflug wrote:
> Richard Huxton wrote:
>> Richard Huxton wrote:
>>> Heikki Linnakangas wrote:

<snip>

>>> 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..

Thanks for the pointer.

> "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.

Is the above description about UPDATE or DELETE operations?
AFAIR SELECT FOR UPDATE operations avoided the incosistency from the
first for joins though I'm not sure about subqueries.
Or I may be misunderstanding something?

> 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."

<snip>

> 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.

It seems pretty difficult for PostgreSQL rule system to avoid such
kind of updates. I'm suspicious if UPDATABLE VIEWS can be implemented
using the rule system.

regards,
Hiroshi Inoue