Re: Bug? Function with side effects not evaluated in CTE

In terms of "expected" or "surprising" behavior, I don't think you can say ANY behavior could be expected. SQL is designed to be declarative. When it comes to retrieval (which is the issue originally raised since this involves a SELECT before the modification), you tell it what you want, and some engine figures out the best way to retrieve it. The engine is allowed to make whatever optimizations it chooses as long as the result set is correct. So if you really want to modify something, be explicit and don't drop a function with side effects in the middle of a complex query like this. God only knows what the engine will do with that.

In my opinion, the simplest and most correct way to handle this is to document that there are no guarantees about what will happen with volatile functions in these strange cases. PostgreSQL shouldn't have to make guarantees about whether functions are evaluated in CTEs or what have you; it should have the freedom to optimize those things away or not.