Re: functions marked STABLE not allowed to do INSERT

On Monday 14 November 2005 18:36, Tom Lane wrote:
> 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.
>

It's hard to be convincing when you start out thinking the other side to be
fools.

> 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.
>

The basic scenario is one of a function that, given input, looks up
corresponding information in a cache table. If it can't find the
information, it goes through a more complicated (and slower) search to obtain
the information, inserts that information into the cache, and returns the
result. Note it always returns the same result whether the cache contains
the information or not, which means you really do only need to evaluate it
once per scan. The problem is that when you mark such functions as volatile
the performance you get is horrendous, so you're forced to mark them as
stable so the planner will make use of index scans and such and give decent
performance. Now maybe that's not a convincing use-case, but it is a common
one.

--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL