Re: functions marked STABLE not allowed to do INSERT

Robert Treat <xzilla(at)users(dot)sourceforge(dot)net> writes:
> The previous discussion/complaints really revolved around how volatility
> effected the planner. There are some scenarios (most revolving around a
> surrogate key lookup type scenario) where 99% of function calls do not
> generate DML changes and because of that we need the planner to treat these
> functions as stable functions rather than volatile functions (and we're aware
> of the tradeoffs of the other 1% case, but willing to take the hit). At the
> time the check was instituted inside plpgsql, istr some of us saying that we
> needed a 4th volatility that meant "treat my as stable for purposes of the
> planner, but treat me as volatile for other purposes" but the proposals never
> gathered much steam.

Probably because you never provided a convincing use-case.

As far as the planner is concerned, the only real differences between
stable and volatile functions are:
1. A stable function is safe to use in an indexscan qualification
(which implies it will be evaluated only once per scan, not once
per row, but *only* if the relevant index actually gets used).
2. Stable functions are OK to evaluate speculatively when trying to
estimate WHERE-clause selectivities.

It's tough to believe that a function with side-effects is reasonable to
use in either of those ways (and no, "it only changes the database 1% of
the time" doesn't make it more reasonable). In fact, I'd go so far as
to say that you're a fool if you use a function with side-effects in a
WHERE clause, ever --- but doubly so if you then want to claim to the
planner that it hasn't got any side-effects.

Now, the current discussion about stable functions really has to do with
semantics of SQL-command evaluation within the function itself, which is
only weakly related to what the planner thinks about it. So it's not
a-prior impossible that we've overloaded the meaning of "stable" too
much and should split the concepts somehow. But it's not clear to me
why or how, which is why I'm wanting a plausible use-case.

regards, tom lane