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

Thank you for all of the responses. This was really helpful.

Damon

On Sat, Oct 16, 2010 at 12:54 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:

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