We have migrated to new Google sites!
Post date: Jan 15, 2015 4:0:38 PM
Functions usage in Greenplum is limited because of its shared nothing architecture. It is possible to execute volatile and stable functions only when they can be run on the master node - for consistency reasons. The only functions you can execute at segment node are immutable.
IMMUTABLE indicates that the function cannot modify the database and always returns the same result when given the same argument values. 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 values (such as the current time zone), and so on. 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().
Also the function used in the greenplum trigger must be IMMUTABLE, meaning it cannot use information not directly present in its argument list. The function specified in the trigger also cannot execute any SQL or modify distributed database objects in any way.