Moshe Jacobson <moshe@neadwerx.com> writes:
> On Mon, Oct 21, 2013 at 2:53 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Consider
>>
>> SELECT volatile_function(i) FROM generate_series(1, 10) i LIMIT 1;
>>
>> How many times should the volatile function get executed? If your answer
>> is not "10", how is this different from the CTE case? This LIMIT clause
>> is restricting the number of times the function executes in pretty much
>> the same way that our definition of CTE evaluation does, AFAICS.
> I don't think your example above is analogous, because in your example, you
> are asking *how many times* to execute the function, whereas in my example,
> the question is *whether* to execute the query at all.
No, it's about how many times to execute it, ie, how many rows to pull
from the CTE. In particular, the optimization you're complaining about
is an early-termination rule that's not fundamentally different IMO from
what LIMIT does.
More generally, what you're arguing for is that the executor's behavior
should change depending on whether a query contains a volatile function.
That's a direction I think we shouldn't go in. Up to now, the presence of
volatile functions has been something that can disable particular planner
optimizations, but it's not of concern to the executor.
regards, tom lane