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