Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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:
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]


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux