Thread: Function Volatility

<p dir="LTR"><span lang="es-ar"></span><span lang="es-ar"><font face="Arial" size="2">Hi guys,</font></span><p
dir="LTR"><spanlang="es-ar"></span><span lang="es-ar"></span><p dir="LTR"><span lang="es-ar"></span><span
lang="es-ar"></span><spanlang="en-us"><font face="Arial" size="2">I am not sure if I am understanding
volatility.</font></span><spanlang="es-ar"></span><span lang="es-ar"></span><span lang="en-us"></span><p
dir="LTR"><spanlang="es-ar"></span><span lang="es-ar"></span><span lang="en-us"><font face="Arial"
size="2">My</font></span><spanlang="es-ar"></span><span lang="es-ar"></span><span lang="en-us"> <font face="Arial"
size="2">issue</font></span><spanlang="es-ar"></span><span lang="es-ar"></span><span lang="en-us"><font face="Arial"
size="2">is better explained with a quick example.</font></span><span lang="es-ar"></span><span
lang="es-ar"></span><spanlang="en-us"> <font face="Arial" size="2">The function</font></span><span
lang="es-ar"></span><spanlang="es-ar"></span><span lang="en-us"> <font face="Arial" size="2">below expresses call
durationsin minutes and it</font></span><span lang="es-ar"></span><span lang="es-ar"></span><span lang="en-us"> <font
face="Arial"size="2">is immutable.</font></span><span lang="es-ar"></span><span lang="es-ar"></span><span
lang="en-us"></span><pdir="LTR"><span lang="es-ar"></span><span lang="es-ar"></span><span lang="en-us"></span><p
dir="LTR"><spanlang="es-ar"></span><span lang="es-ar"></span><span lang="en-us"><font face="Arial" size="2">CREATE OR
REPLACEFUNCTION dur2min(</font></span><span lang="es-ar"></span><span lang="es-ar"></span><span lang="en-us"><font
face="Arial"size="2">secs</font></span><span lang="es-ar"></span><span lang="es-ar"></span><span lang="en-us"><font
face="Arial"size="2"> INTEGER) RETURNS INTEGER</font></span><p dir="LTR"><span lang="en-us"><font face="Arial"
size="2">AS$$</font></span><p dir="LTR"><span lang="en-us"><font face="Arial" size="2">BEGIN</font></span><p
dir="LTR"><spanlang="en-us">        <font face="Arial" size="2">RAISE NOTICE 'BEEN HERE!';</font></span><p
dir="LTR"><spanlang="en-us">        <font face="Arial" size="2">RETURN CEIL(</font></span><span
lang="es-ar"></span><spanlang="es-ar"></span><span lang="en-us"><font face="Arial" size="2">secs</font></span><span
lang="es-ar"></span><spanlang="es-ar"></span><span lang="en-us"><font face="Arial" size="2">/60.0);</font></span><p
dir="LTR"><spanlang="en-us"><font face="Arial" size="2">END;</font></span><p dir="LTR"><span lang="en-us"><font
face="Arial"size="2">$$ LANGUAGE 'plpgsql' IMMUTABLE;</font></span><span lang="es-ar"></span><span
lang="es-ar"></span><spanlang="en-us"></span><p dir="LTR"><span lang="es-ar"></span><span lang="es-ar"></span><span
lang="en-us"></span><pdir="LTR"><span lang="es-ar"></span><span lang="es-ar"></span><span lang="en-us"></span><p
dir="LTR"><spanlang="es-ar"></span><span lang="es-ar"></span><span lang="en-us"><font face="Arial" size="2"># SELECT
dur2min(30)as c1, dur2min(30) as c2, dur2min(30) as c3;</font></span><p dir="LTR"><span lang="es-ar"></span><span
lang="es-ar"></span><spanlang="en-us"></span><p dir="LTR"><span lang="es-ar"></span><span lang="es-ar"></span><span
lang="en-us"><fontface="Arial" size="2">NOTICE:  BEEN HERE!</font></span><p dir="LTR"><span lang="en-us"><font
face="Arial"size="2">NOTICE:  BEEN HERE!</font></span><p dir="LTR"><span lang="en-us"><font face="Arial"
size="2">NOTICE: BEEN HERE!</font></span><p dir="LTR"><span lang="en-us"><font face="Arial" size="2"> c1 | c2 |
c3</font></span><pdir="LTR"><span lang="es-ar"></span><span lang="es-ar"><font face="Arial"
size="2">----+----+----</font></span><pdir="LTR"><span lang="es-ar"><font face="Arial" size="2">  1 |  1 | 
1</font></span><pdir="LTR"><span lang="es-ar"><font face="Arial" size="2">(1 row)</font></span><span
lang="es-ar"></span><spanlang="es-ar"></span><p dir="LTR"><span lang="es-ar"></span><span lang="es-ar"></span><span
lang="en-us"><fontface="Arial" size="2">Wh</font></span><span lang="es-ar"></span><span lang="es-ar"></span><span
lang="en-us"><fontface="Arial" size="2">at</font></span><span lang="es-ar"></span><span lang="es-ar"></span><span
lang="en-us"><font face="Arial" size="2">bother</font></span><span lang="es-ar"></span><span lang="es-ar"></span><span
lang="en-us"><fontface="Arial" size="2"> me</font></span><span lang="es-ar"></span><span lang="es-ar"></span><span
lang="en-us"><font face="Arial" size="2">are</font></span><span lang="es-ar"></span><span lang="es-ar"></span><span
lang="en-us"><fontface="Arial" size="2"> the 3</font></span><span lang="es-ar"></span><span lang="es-ar"></span><span
lang="en-us"><font face="Arial" size="2">“</font></span><span lang="es-ar"></span><span lang="es-ar"></span><span
lang="en-us"><fontface="Arial" size="2">been here</font></span><span lang="es-ar"></span><span
lang="es-ar"></span><spanlang="en-us"><font face="Arial" size="2">”</font></span><span lang="es-ar"></span><span
lang="es-ar"></span><spanlang="en-us"><font face="Arial" size="2"> messages. As the function is immutable and the
parameterremains unchanged</font></span><span lang="es-ar"></span><span lang="es-ar"></span><span lang="en-us"> <font
face="Arial"size="2">needs</font></span><span lang="es-ar"></span><span lang="es-ar"></span><span lang="en-us"><font
face="Arial"size="2"> the planner</font></span><span lang="es-ar"></span><span lang="es-ar"></span><span
lang="en-us"><fontface="Arial" size="2"> actually execute the function 3 times?</font></span><p dir="LTR"><span
lang="en-us"><fontface="Arial" size="2">I was under the impression that</font></span><span lang="es-ar"></span><span
lang="es-ar"></span><spanlang="en-us"><font face="Arial" size="2"> under these conditions</font></span><span
lang="es-ar"></span><spanlang="es-ar"></span><span lang="en-us"><font face="Arial" size="2"> it could
*</font></span><spanlang="es-ar"><b></b></span><span lang="es-ar"><b></b></span><b><span lang="en-us"><font
face="Arial"size="2">reuse</font></span></b><span lang="es-ar"></span><span lang="es-ar"></span><span
lang="en-us"><fontface="Arial" size="2">* the result of the</font></span><span lang="es-ar"></span><span
lang="es-ar"></span><spanlang="en-us"><font face="Arial" size="2"> first call</font></span><span
lang="es-ar"></span><spanlang="es-ar"></span><span lang="en-us"><font face="Arial" size="2">. The
manual</font></span><spanlang="es-ar"></span><span lang="es-ar"></span><span lang="en-us"> <font face="Arial"
size="2">states</font></span><spanlang="es-ar"></span><span lang="es-ar"></span><span lang="en-us"> <font face="Arial"
size="2">theplanner</font></span><span lang="es-ar"></span><span lang="es-ar"></span><span lang="en-us"> <font
face="Arial"size="2">should</font></span><span lang="es-ar"></span><span lang="es-ar"></span><span lang="en-us"><font
face="Arial"size="2"> avoid</font></span><span lang="es-ar"></span><span lang="es-ar"></span><span lang="en-us"><font
face="Arial"size="2"> reevaluate the function but I</font></span><span lang="es-ar"></span><span
lang="es-ar"></span><spanlang="en-us"><font face="Arial" size="2">’</font></span><span lang="es-ar"></span><span
lang="es-ar"></span><spanlang="en-us"><font face="Arial" size="2">m not sure what that means as it *</font></span><span
lang="es-ar"><b></b></span><spanlang="es-ar"><b></b></span><b><span lang="en-us"><font face="Arial"
size="2">is</font></span></b><spanlang="es-ar"></span><span lang="es-ar"></span><span lang="en-us"><font face="Arial"
size="2">*executing it every time.</font></span><span lang="es-ar"></span><span lang="es-ar"></span><span
lang="en-us"></span><pdir="LTR"><span lang="es-ar"></span><span lang="es-ar"></span><span lang="en-us"></span><p
dir="LTR"><spanlang="en-us"><font face="Arial" size="2">My goal of course is that the function get</font></span><span
lang="es-ar"></span><spanlang="es-ar"></span><span lang="en-us"><font face="Arial" size="2">s</font></span><span
lang="es-ar"></span><spanlang="es-ar"></span><span lang="en-us"><font face="Arial" size="2"> executed only once per
row.</font></span><spanlang="es-ar"></span><span lang="es-ar"></span><span lang="en-us"></span><p dir="LTR"><span
lang="en-us"><fontface="Arial" size="2">I</font></span><span lang="es-ar"></span><span lang="es-ar"></span><span
lang="en-us"><fontface="Arial" size="2">’</font></span><span lang="es-ar"></span><span lang="es-ar"></span><span
lang="en-us"><fontface="Arial" size="2">m using 8.2.4</font></span><span lang="es-ar"></span><span
lang="es-ar"></span><spanlang="en-us"></span><p dir="LTR"><span lang="en-us"><font face="Arial" size="2">Thanks for
yourhindsight.</font></span><p dir="LTR"><span lang="en-us"><font face="Arial" size="2">Regards,</font></span><p
dir="LTR"><spanlang="en-us"><font face="Arial" size="2">Fernando.</font></span><span lang="es-ar"></span><span
lang="es-ar"></span><spanlang="en-us"></span> 

"Fernando Hevia" <fhevia@ip-tel.com.ar> writes:
> I am not sure if I am understanding volatility.

You're not.

> What bother me are the 3 "been here" messages. As the function is immutable
> and the parameter remains unchanged needs the planner actually execute the
> function 3 times?

The IMMUTABLE marker is a promise from you to the system that it is safe
to optimize away multiple calls to the function.  It is not a promise
from the system to you that the system will expend unlimited amounts of
energy to detect duplicate calls.  The majority of the immutable
functions in Postgres are things like int4pl(), where it would obviously
be silly to expend any cycles at all on looking for duplicate calls such
as you show here.

In practice what will happen is that each textual call will be folded to
a constant separately.  The advantage comes from not having to repeat
the call for each row processed by a query, not from saving work within
a row.
        regards, tom lane

Tom Lane writes:

> The IMMUTABLE marker is a promise from you to the system that it is safe
> to optimize away multiple calls to the function.  It is not a promise
> from the system to you that the system will expend unlimited amounts of
> energy to detect duplicate calls.

Nicely put. Thanks!
BTW, this explanation should go into the manual.

Regards,
Fernando.