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

On Mon, Oct 11, 2010 at 7:10 PM, Damon Snyder <damon@huddler-inc.com> wrote:
> Hello,
> I have heard it said that if a stored procedure is declared as VOLATILE,
> then no good optimizations can be done on queries within the stored
> procedure or queries that use the stored procedure (say as the column in a
> view). I have seen this in practice, recommended on the irc channel, and in
> the archives
> (http://archives.postgresql.org/pgsql-performance/2008-01/msg00283.php). Can
> someone help me understand or point me to some documentation explaining why
> this is so?
> Any insights would be appreciated. I'm new to pgsql and would like to know a
> little more about what is going on under the hood.
> Thanks,
> Damon

The theory behind 'volatile' is pretty simple -- each execution of the
function, regardless of the inputs, can be expected to produce a
completely independent result, or modifies the datbase.  In the case
of immutable, which is on the other end, particular set of inputs will
produce one and only result, and doesn't modify anything.

In the immutable case, the planner can shuffle the function call
around in the query, calling it less, simplifying joins, etc.  There
are lots of theoretical optimizations that can be done since the
inputs (principally table column values and literal values) can be
assumed static for the duration of the query.

'stable' is almost like immutable, but is only guaranteed static for
the duration of the query.  most functions that read from but don't
write to the database will fit in this category.  Most optimizations
still apply here, but stable functions can't be used in indexes and
can't be executed and saved off in plan time where it might be helpful
(prepared statements and pl/pgsql plans).

broadly speaking:
*) function generates same output from inputs regardless of what's
going on in the database, and has no side effects: IMMUTABLE
*) function reads (only) from tables, or is an immutable function in
most senses but influenced from the GUC (or any other out of scope
thing): STABLE
*) all other cases: VOLATILE (which is btw the default)

merlin

> broadly speaking:
> *) function generates same output from inputs regardless of what's
> going on in the database, and has no side effects: IMMUTABLE

So can I say "if a function is marked IMMUTABLE, then it should never
modify database"? Is there any counter example?

> *) function reads (only) from tables, or is an immutable function in
> most senses but influenced from the GUC (or any other out of scope
> thing): STABLE

It seems if above is correct, I can say STABLE functions should never
modify databases as well.

> *) all other cases: VOLATILE (which is btw the default)
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

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

            regards, tom lane

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.

merlin