Thank you for all of the responses. This was really helpful.
Damon
On Sat, Oct 16, 2010 at 12:54 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
On Fri, Oct 15, 2010 at 10:31 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Tatsuo Ishii <ishii@postgresql.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.
