Re: [HACKERS] Strange transaction-id behaviour? (was Re: Two updates problem)

Richard Huxton <dev(at)archonet(dot)com> 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