Re: functions marked STABLE not allowed to do INSERT

Tino Wildenhain <tino(at)wildenhain(dot)de> writes:
> Now this is really a bug:

That's in the eye of the beholder (and one who wasn't paying attention
to previous discussion of this point, evidently).

The reason why the no-data-change rule is now enforced, not only
recommended, is that a stable/immutable function now actually would
not see any changes it did make. Consider code like

INSERT INTO foo VALUES (42, ...);
SELECT * INTO rec FROM foo WHERE key = 42;
IF NOT FOUND THEN
RAISE EXCEPTION 'where did my row go?';

If this were allowed in stable/immutable functions, the RAISE would
in fact be reached in 8.1, because the SELECT will be done with the
snapshot of the query that called the function. This is a feature,
not a bug, because it makes it possible to write a stable function
that selects from the database and be sure that it really is stable
in the face of concurrent changes.

Calling a volatile function that itself makes some database changes
isn't necessarily a wrong thing to do; the rule is just that the
calling stable function isn't going to see those changes, just as
the outer query won't (and never has).

In a larger sense, maybe we ought to forbid stable/immutable functions
calling volatiles, but it's not clear that there are no cases where it
makes sense. As Robert notes, the lack of this check does provide an
"out" for people who want to do what you want to do.

regards, tom lane