Richard Huxton <dev@xxxxxxxxxxxx> writes: > I'm not sure it's sensible to have the update in the WHERE clause - I > don't know that you can depend on how many times that function will be > called. It's absolutely not very sensible to do that ... note the warnings in http://www.postgresql.org/docs/8.0/static/sql-expressions.html#SYNTAX-EXPRESS-EVAL We have no way to enforce "no functions with side effects in WHERE", but you're not going to get any sympathy at all if you break that rule. > On the other hand, I wouldn't like to say this is the right behaviour - > I'm cc:ing this to the hackers list so they can take a look at it. It is intentional. A given command can only see/update row versions produced by earlier commands --- without this rule, you have the "Halloween problem" that an UPDATE can see (and try to update) its own output rows, leading to an infinite loop. Actually the rule is "you can see row versions produced by commands started earlier than your own command" (cmin < current cid), which means there is another risk involved in this sort of programming: if the function looks at the contents of the table being updated by the outer UPDATE, it will see the partially completed effects of the UPDATE. While I suppose that's exactly what Yuri was after ;-), it's generally considered a bad thing, because there is no guarantee as to the order in which rows are updated, and thus no predictability as to exactly what intermediate states the function will see. As of PG 8.0, things are set up so that this only applies to functions marked VOLATILE; if a function is marked STABLE or IMMUTABLE then it runs with the same cid as the calling query, and therefore it does *not* see any partial effects of that query. Confused yet? ;-) regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
