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

Поиск
Список
Период
Сортировка
От BladeOfLight16
Тема Re: Bug? Function with side effects not evaluated in CTE
Дата
Msg-id CA+=1U=Vf=JRLDSxHb_djv4jx4OSogBOp_kfO1jmejXUT+=87Bg@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Bug? Function with side effects not evaluated in CTE  (BladeOfLight16 <bladeoflight16@gmail.com>)
Ответы Re: Bug? Function with side effects not evaluated in CTE  (David Johnston <polobo@yahoo.com>)
Список pgsql-general
On Mon, Oct 21, 2013 at 6:52 PM, BladeOfLight16 <bladeoflight16@gmail.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.

В списке pgsql-general по дате отправления:

Предыдущее
От: AI Rumman
Дата:
Сообщение: how to get the connected session pointer ( Archive * AH)
Следующее
От: David Johnston
Дата:
Сообщение: Re: Bug? Function with side effects not evaluated in CTE