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

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(dot)collins(at)gmail(dot)com>wrote:

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