Re: function side effects

On Mon, 2010-02-22 at 23:49 -0500, Tom Lane wrote:
> Tatsuo Ishii <ishii(at)postgresql(dot)org> writes:
> > I'm wondering if we could detect a funcion has a side effect,
> > i.e. does a write to database.
>
> > Currently we have three properties of functions: IMMUTABLE, STABLE and
> > VOLATILE. According to docs IMMUTABLE or STABLE functions do not write
> > to database.
>
> Those classifications are meant as planner directives; they are NOT
> meant to be bulletproof.

You make them sound like "hints". (I thought we frowned on those?)

That isn't true, they don't just change the optimal plan in the way the
enable_* parameters do. Immutable functions are reduced in ways that
would give the wrong answer if the function is actually volatile.
Referring to function properties as "planner directives" hides their
critical importance to the output of a query that calls such functions.

> Hanging database integrity guarantees on
> whether a "non volatile" function changes anything is entirely unsafe.
> To give just one illustration of the problems, a nonvolatile function
> is allowed to call a volatile one.

So wrongly marking a function as something other than volatile *is* a
data integrity issue. Why is that OK? ISTM that this should work the way
Tatsuo wants it to work. Immutability should be passed down through the
call stack to ensure we can't get this wrong.

If people have been advising clients to set things immutable when they
are not that seems fairly questionable. We shouldn't avoid fixing an
integrity loophole just simply to preserve a planner backdoor,
especially since other backdoors are specifically avoided.

--
Simon Riggs www.2ndQuadrant.com