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.
