I'm unclear about the true meaning in the definitions for IMMUTABLE, VOLATILE and STABLE functions.

I read the documentation, specifically the definitions of each.

IMMUTABLE indicates that the function cannot modify the database and always returns the same result when given the same argument values; that is, it does not do database lookups or otherwise use information not directly present in its argument list. If this option is given, any call of the function with all-constant arguments can be immediately replaced with the function value.

STABLE indicates that the function cannot modify the database, and that within a single table scan it will consistently return the same result for the same argument values, but that its result could change across SQL statements. This is the appropriate selection for functions whose results depend on database lookups, parameter variables (such as the current time zone), etc. (It is inappropriate for AFTER triggers that wish to query rows modified by the current command.) Also note that the current_timestamp family of functions qualify as stable, since their values do not change within a transaction.

VOLATILE indicates that the function value can change even within a single table scan, so no optimizations can be made. Relatively few database functions are volatile in this sense; some examples are random(), currval(), timeofday(). But note that any function that has side-effects must be classified volatile, even if its result is quite predictable, to prevent calls from being optimized away; an example is setval().

My confusion comes in with the condition for IMMUTABLE and STABLE that the function ALWAYS or CONSISTENTLY returns the same result given the same arguments.

The IMMUTABLE definition states that the function does not database lookups or otherwise use information not directly present in its argument list. So, to me, that means such functions are used to manipulate data provided by the client, and should not have SELECT statements...although that just sounds a bit odd to me.

With STABLE, the definition is similar in that it says it should consistently return the same result. So, to me, that means that everytime the function is called with the same arguments, it should return the same results (same exact rows, every single time).

So, to me...that means that any function that performs a SELECT on a table or tables that can be updated, should only be volatile.

But, again...that doesn't sound right to me.

Bringing this back to my use-case, I am writing functions that perform SELECT statements with multiple JOIN's on tables that are constantly being added to, so the function calls would be expected to return different results each time it's called, even with the same arguments.

So, does that mean that my functions should be VOLATILE? Even though the documentation indicates relatively few database functions are volatile in this sense?

Thank you!

up vote 7 down vote accepted

IMMUTABLE must be a pure function, whose results depend only on its inputs. This is a very strict requirement; they cannot call other non-immutable functions, they cannot access tables, they cannot access the value of configuration properties, etc.

STABLE can use any inputs that are themselves STABLE: other STABLE or IMMUTABLE functions, and SELECT queries of tables. It's safe to query tables because the function's view of those tables will not change within the query's current snapshot. You can access GUC values (current_setting(...)) so long as you know they won't also be assigned to within the current statement.

VOLATILE functions are everything that doesn't fit the above:

  • Anything with side-effects
  • Anything that does writes
  • Anything that queries external data not managed by the PostgreSQL snapshot
  • ...

In general, just leave everything VOLATILE unless you have a good reason not to.

The main reason to use IMMUTABLE is when writing functions that are to be used as part of index expressions.

  • "they cannot access tables." In fairness, they can and they do. I think a more general rule is that the tables are not supposed to meaningfully mutate without database-restart. – Evan Carroll Sep 5 '17 at 3:26
  • If STABLE allows table access, are there any optimizations over/above VOLATILE...? – Brooks Sep 5 '17 at 15:12
  • Don't remember off the top of my head, would have to check docs/code. – Craig Ringer Sep 6 '17 at 0:51

For STABLE, the part you need to bold is 'result could change across SQL statements'

IMMUTABLE things are not supposed to change ever. Even if you restart your database server, run yum update (but of course there can be bugs!), change your configuration (like datestyle, timezone, default_text_search_config, extra_float_digits, etc.), or replace your server hardware entirely (of the same architecture as the old hardware, so the binary files are still compatible).

The functions you describe sound like they are STABLE, because within a single SQL statement they will execute their queries using the same snapshot as the outer query has, and so any concurrent changes you made to those other tables would not be visible. Now, if your functions opened a new connection to the server and run their queries within that independent connection, that would make the function volatile, because they would be using different snapshots.

  • I believe I understand the pre-requisites for IMMUTABLE (nothing can change....ever, between queries, connections, reboots, planetary destruction and reconstruction, EVEN IF the database is modified) and VOLATILE (the function jumps outside of the context in which it was called). Is that correct? So, then it appears STABLE simply means the function does not modify the database and does not access the database outside of its context? The definition of STABLE feels like it's WAY more complicated than it really needs to be...Or am I leaving something out? – Brooks Sep 4 '17 at 23:46
  • PostgreSQL actually has some issues around IMMUTABLE and collations. It trusts that glibc (or, in newer Pg, iconv) won't change collation definitions. In reality, they do, and provide no way to detect such changes. It can lead to silent index corruption :( . It's mostly an issue when replicating between different OS versions, etc. – Craig Ringer Sep 5 '17 at 2:36

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.