I thought this was interesting, and wanted to make sure I understood what is going on, but the more tests I run the more confused I get.
if I take the exact set up outlined by Mosche I get the same results in 9.3 (as expected) , but if I insert one row before I run the sql the CTE is executed and I get a new row in the table. I was hoping that I would see a difference in the explain, but the explain with an empty table where the CTE is not executed is identical to the explain where there is one row in the table already and the CTE is executed resulting in a new row. I thought maybe Postgres was not executing the CTE because it knows that there are no rows in the table for it to delete, however if I change the CTE to be an insert returning instead of a function I get different results. Even when the table is empty I get new row created.
I would really like to know "why' it is working like this so something similar does not come back and bite me in the future.
Thanks
On Wed, Oct 16, 2013 at 7:14 PM, Rowan Collins <rowan.collins@xxxxxxxxx> wrote:
This sounds reasonable for a "yes or no" case like this, but wouldn't it raise the question of how many times the function should be evaluated?On 17/10/2013 00:06, Merlin Moncure wrote:
That being said, I do think it might be better behavior (and still technically correct per the documentation) if volatile query expressions were force-evaluated.
What if the query looked more like this:
with tt_created as
(
select fn_new_item(foo) as item
from some_huge_table
)
select item
from tt_created
limit 10
Should the CTE be calculated in its entirety, running the function for every row in some_huge_table? Or should it run at most 10 times?
Which is desired would depend on the situation, but there's no real way to indicate in the syntax.
-- Rowan Collins [IMSoP]
