What are immutable, stable and volatile functions in greenplum

Post date: Nov 26, 2013 6:55:25 PM

There are 3 type of functions. IMMUTABLE, STABLE, or VOLATILE. Greenplum Database offers

1. Full support of all IMMUTABLE functions

2. STABLE functions is supported in most cases

3. VOLATILE functions is restricted in Greenplum Database

An immutable function is a function that relies only on information directly present in its argument list and will always return the same result when given the same argument values.

STABLE indicates that within a single table scan the function will consistently return the same result for the same argument values, but that its result could change across SQL statements. Functions whose results depend on database lookups or parameter variables are classified as STABLE. "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. 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 (for example, setval()).

Note: In Greenplum Database, the data is divided up across the segments — each segment is, in a sense, its own distinct PostgreSQL database. To prevent data from becoming out-of-sync across the segments, any function classified VOLATILE cannot be executed at the segment level if it contains SQL or modifies the database in any way. For example, functions such as random() or timeofday() are not allowed to execute on distributed data in Greenplum Database because they could potentially cause inconsistent data between the segment instances. To ensure data consistency, VOLATILE and STABLE functions can safely be used in statements that are evaluated on and execute from the master. For example, the following statements are always executed on the master (statements without a FROM clause):

SELECT setval('myseq', 201); SELECT foo();

In cases where a statement has a FROM clause containing a distributed table and the function used in the FROM clause simply returns a set of rows, execution may be allowed on the segments:

SELECT * from foo();

One exception to this rule are functions that return a table reference (rangeFuncs) or functions that use the refCursor datatype. These types of functions cannot be used at all in Greenplum Database.

Greenplum Database supports user-defined functions as does PostgreSQL. By default, functions are declared as VOLATILE, so it is important to specify the correct volatility level if you are registering a user-defined function that is IMMUTABLE or STABLE. When creating user-defined functions, avoid using fatal errors or any kind of destructive call. The Greenplum Database server may respond to such errors with a sudden shutdown or restart. Note that in Greenplum Database, the shared library files for user-created functions must reside in the same library path location on every host in the Greenplum Database array (masters, segments, and mirrors).