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

On Mon, Oct 21, 2013 at 6:52 PM, BladeOfLight16 <bladeoflight16(at)gmail(dot)com>wrote:

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

I'd like to add one thing. I really appreciate that the maintainers of
PostgreSQL are so open to there being use cases for seemingly weird things.
They try as hard as they can to avoid answering behavior questions with,
"Of course it doesn't work; you shouldn't do that." The prevalence of that
kind of thinking in the Oracle community is something that causes me a lot
of grief since I have to work with Oracle regularly, so I know how valuable
PostgreSQL's work at being intuitive even in seemingly weird cases is. This
is a special case, though. The "right behavior" isn't even close to clear
here; there isn't even a majority consensus as near as I can tell. There
are too many weird edge cases to account for them all. When the "right
behavior" isn't even close to clear like this, I think it's better to
simply avoid the issue entirely and discourage people from depending on any
kind of particular behavior.

Regarding UPSERT in particular, are you working with a single row or a set
of rows? If a single row, is there a reason you can't perform a SELECT
before hand to see if the PK is already there and then INSERT or UPDATE
accordingly? If multiple rows, is there a reason you can't UPDATE ...
SELECT * FROM rows WHERE pk IN ... and then INSERT ... SELECT * FROM rows
WHERE pk NOT IN ...? It seems to me that would be more
readable/maintainable than relying on a particular CTE behavior.