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


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


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.

Hiroshi Inoue

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Reply via email to