Re: Function Volatility and Views Unexpected Behavior

On Wed, Jul 12, 2017 at 3:23 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> David Kohn <djk447(at)gmail(dot)com> writes:
>> I encountered some unexpected behavior when debugging a query that was
>> taking longer than expected, basically, a volatile function that makes a
>> column in a view is called even when that column is not selected in the
>> query, making it so that the function is called for every row in the view,
>> I'm not sure that that would necessarily be the expected behavior, as it
>> was my understanding that columns that are not selected are not evaluated,
>> for instance if there was a join in a view that produced some columns and
>> said columns were not selected, I would expect it to be optimized away.
>
> No, this is the expected behavior; we don't like optimization to change
> the number of calls of a volatile function from what would occur in naive
> evaluation of the query. If that prospect doesn't bother you, it's
> likely because your function isn't really volatile ...

I don't think I agree with that. If something is VOLATILE, that means
you want it to be recalculated each time, but it doesn't necessarily
mean that you want it calculated if it in no way changes the result
set.

I guess maybe there's a difference between a VOLATILE function like
random(), which is expected to produce a different answer each time
but probably has no side effects that you care about (unless you care
about the fact that the state of the PRNG has changed) and pg_sleep(),
whose return value is always the same but whose side effects are of
critical importance. Maybe we need separate terms for
volatile-because-the-answer-is-unstable and
volatile-because-it-has-side-effects.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company