Re: Stored procedure declared as VOLATILE => no good optimization is done

On Fri, Oct 15, 2010 at 10:31 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Tatsuo Ishii <ishii(at)postgresql(dot)org> writes:
>> So can I say "if a function is marked IMMUTABLE, then it should never
>> modify database"? Is there any counter example?
>> It seems if above is correct, I can say STABLE functions should never
>> modify databases as well.
>
> Both of those things are explicitly stated here:
> http://developer.postgresql.org/pgdocs/postgres/xfunc-volatility.html

Ok, being pedantic here, but:

I think more interesting is *why* the 'immutable shall not modify the
database' requirement is there. IOW, suppose you ignore the warnings
on the docs and force immutability on a function that writes (via the
function loophole) to the database, why exactly is this a bad idea?
The reasoning given in the documentation explains a problematic
symptom of doing so but gives little technical reasoning what it
should never be done.

One reason why writing to the database breaks immutability is that
writing to the database depends on resources that can change after the
fact: function immutability also pertains to failure -- if a function
errors (or not) with a set of inputs, it should always do so. If you
write to a table, you could violate a constraint from one call to the
next, or the table may not even be there at all...

Writing to the database means you are influencing other systems, and
via constraints they are influencing you, so it makes it wrong by
definition. That said, if you were writing to, say, a table with no
meaningful constraints this actually wouldn't be so bad as long as you
can also deal with the other big issue with immutability, namely that
there is not 1:1 correspondence between when the function is logically
evaluated and when it is executed. This more or less eliminates
logging (at least outside of debugging purposes), the only thing I can
figure you can usefully do on a table w/no enforceable constraints.
Also, a big use case for immutable function is to allow use in
indexing, and it would be just crazy (again, debugging purposes aside)
to write to a table on index evaluation.

merlin