Tom Lane-2 wrote > try a set-returning function in the > select list to see that this is true. Random thoughts... Noted - though then there appears to be various optimizations at play here then... [somewhat dated 9.0.X version] SELECT x, generate_series(x, 5) AS y FROM generate_series(1,3) gs (x) LIMIT 6 QUERY PLAN Limit (cost=0.00..0.08 rows=6 width=4) (actual time=0.011..0.015 rows=6 loops=1) -> Function Scan on generate_series gs (cost=0.00..12.50 rows=1000 width=4) (actual time=0.011..0.015 rows=6 loops=1) Total runtime: 0.031 ms ...unfortunately EXPLAIN doesn't provide much help in understanding where. Related to other postings: can the planner generate an instruction for the executor that in effect forces (complete?) execution of a specific node by the executor? The executor doesn't care that it is being told to force execution due to a volatile function declaration or any other reason is just knows it has to do it. The original case is: WITH cte AS ( volatile_function ) DELETE FROM empty_table How about a compromise position making the above work but without altering existing mechanics? I would suppose that would require new syntax, and the ability for the planner to force the executor to evaluate a CTE expression unconditionally, but it would make the above query idiom work correctly. WITH cte (ALWAYS EXECUTE) AS ( SELECT volatile_function(1) ) DELETE FROM empty_table I guess ignoring implementation considerations is this idiom, and means of executing an UPSERT as noted by the OP, something that is likely to become common given the currently provided CTE functionality. This ability has only been recently added to PostgreSQL and now we are seeing it in action and coming across limitations. In addition to evaluating the technical limitations on why something wasn't implemented initially it is good to get viewpoints on whether these unexpected uses of this feature are something that should be made possible/easier with further enhancements to the system. If the enhancements are desirable then at minimum a ToDo item should be created wherein implementation factors can be discussed. In evaluating the above discussion of whether the current implementation of LIMIT is consistent with the current implementation of CTEs is tabled in favor of discussing whether there is a demonstrable need/desire for having some functions always evaluated when inside a CTE. If so maybe altering the function definition, instead of the CTE, would be a valid solution. But if one is focused on why the CTE behaves the way it does one may tend to lose sight of trying to solve the larger problem in preference to trying to justify maintaining the status-quo. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Bug-Function-with-side-effects-not-evaluated-in-CTE-tp5774792p5775329.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
