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

On Mon, Oct 21, 2013 at 2:51 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Moshe Jacobson <moshe(at)neadwerx(dot)com> writes:
>> On Mon, Oct 21, 2013 at 2:53 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> 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.

Well, I think it *is* different precisely because it is in a CTE;
that's really the whole point. LIMIT is expressly declared and has
obvious (well, mostly) user visible semantics.

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

Ah -- rats. Well, it (arguably) should be concerned but I see your point.

merlin