Greenplum中的函数

在GREENPLUM中函数大致分为三种类型: IMMUTABLE, STABLE, VOLATILE

IMMUTABLE

返回值依赖于参数值，只要参数不变，返回值是确定的。

STABLE

返回值依赖于参数值以及执行是否垮语句。

在参数值相同的情况下，多次执行该函数返回结果可能会不同。像 current_timestamp这一类函数就是STABLE的，在同一个事务中它们的返回值是确定的。但是不同事务间多次执行相同的语句返回结果就可能有所不同。

VOLATILE

即便是在同一个事务同一条语句中，这类函数的返回值也可能有所不同。

 

在GREENPLUM中，由于是分布式数据库，为了保证节点之间的一致性，STABLE和VOLATILE两类函数是不能在SEGMENT级别执行的。

用户自定义的函数如果不指定类别默认是VOLATILE的。

下面是摘自GP ADMIN文档中的一些内容

 

A function can be one of three types: IMMUTABLE, STABLE, or VOLATILE. Greenplum Database offers full support of all IMMUTABLE functions. 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.
The use of STABLE and VOLATILE functions is restricted in Greenplum Database. 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. 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. 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()).
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 as STABLE or 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.

##########################################################

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().