David Johnston wrote:
> Understood (I'm guessing there is no "global" cache but simply the
> plan-level cache that gets populated each time?)
>
> However, in the following example the ps3(2) expression should also qualify
> for this "folding" and thus the RAISE NOTICE should also appear during plan
> time for the same reason; which, per the OP, it does not.
>
> pgb=# select coalesce( ps3(1), ps3(2) );
> WARNING: Call ps3(1)=1
> coalesce
> ----------
> 1
> (1 row)
>
> It would seem the addition of the sub-select messes with the COALESCE logic.
> If the function call is directly a part of the COALESCE statement it can be
> optimized away by the COALESCE logic but if it is buried within a SELECT
> statement the planner does not know that the function is indirectly part of
> a COALESCE input set and so it goes ahead and performs its optimization but
> pre-executing the function and caching its results.
Declaring the function IMMUTABLE asserts that there are no
user-visible side effects, so the database engine is free to
rearrange the code. At that point, one can hardly complain about a
rearrangement that causes the side effects to happen at unexpected
times. Neither is any particular optimization affecting when the
side effects appear *required*.
-Kevin
