一、Oracle 中类似的函数稳定性关键字（DETERMINISTIC）

在 Oracle 里， function 有着一个 DETERMINISTIC 参数，它表示一个函数在输入不变的情况下输出是否确定，只要输入的参数一样，返回的结果一定是一样的，以保证函数对于任何输入总是完全相同的方式处理参数。 Oracle 的内置函数 UPPER、TRUNC 等都是 DETERMINISTIC 函数。具体例子如下：

----带DETERMINISTIC的现象


create or replace function f_t(i_p int) return number DETERMINISTIC is


      i_rtn number;


    begin


      i_rtn := i_p * dbms_random.value(1,10);


      return i_rtn;


    end;


    







select LEVEL,f_t(1) FROM DUAL CONNECT BY LEVEL<1=10;







----不带DETERMINISTIC的现象







 create or replace function f_t(i_p int) return number is


       i_rtn number;


    begin


       i_rtn := i_p * dbms_random.value(1,10);


       return i_rtn;


    end;


    







select LEVEL,f_t(1) FROM DUAL CONNECT BY LEVEL<1=10;

可以看到，带了 DETERMINISTIC参数的，多次执行的结果都是一样的，能保证函数的稳定性。

二、MogDB 数据库的三种函数稳定性状态

MogDB 数据库的函数虽然没有 DETERMINISTIC 关键字，但是函数也有着类似的三种状态的关键字，分别是 IMMUTABLE 、 STABLE 和 VOLATILE 。合理使用这三种不同的状态可以显著改善函数性能。

在数据库里使用 \d create function也可以看到这三种状态的关键字提示：

01

IMMUTABLE(非常稳定)

任何时候调用，只要函数的参数不变结果就不变。纯函数，执行结果可能会在规划时被预求值并缓存。

允许优化器在一个查询用常量参数调用该函数时，提前计算该函数。

整数加法操作符底层的函数其实就是被被标记为 IMMUTABLE的。

表示该函数不能修改数据库，并且对于给定的参数值总是会返回相同的值。也就是说，它不会做数据库查找或者使用没有在其参数列表中直接出现的信息。 IMMUTABLE和 STABLE非常类似，但是 IMMUTABLE 是指在任何情况下，只要参数一致，结果就一致。

02

STABLE(稳定)

在一个事务中调用时，只要函数的参数不变结果就不变。

STABLE函数不能修改数据库状态，但是对于相同的参数值，它在一次表扫描中将返回相同的结果。因而优化器可以将相同参数的多次调用优化成一次调用。在索引扫描条件中允许使用 STABLE函数。

在大多数情况下使用这个状态。在单个表扫描中，对相同的参数值返回相同的结果，但结果将通过 SQL语句进行更改，最后的输出结果取决于数据库查找或参数值。

current_timestamp系列函数是 STABLE; 值在执行中不会改变。

IMMUTABLE和 STABLE很像，显著的区别是优化器对 IMMUTABLE和 STABLE函数的处理上， IMMUTABLE函数在优化器生成执行计划时会将函数结果替换函数，也就是函数不在输出的执行计划中，取而代之的是一个结果常量。 STABLE函数则不会如此，执行计划里以函数输出。

IMMUTABLE和 STABLE在调用次数上的明显区别可以用如下的测试验证出来：

CREATE OR REPLACE FUNCTION func_out_num() RETURNS INTEGER AS


$$


BEGIN


RAISE NOTICE 'Invoke the func.';


RETURN 6;


END;


$$ LANGUAGE PLPGSQL STABLE;












select func_out_num() from generate_series(1,10);


alter function func_out_num() immutable;


select func_out_num() from generate_series(1,10);

在本例中，当使用 STABLE关键字的时候，会按照调用 10次函数，而当使用 IMMUTABLE关键字时，它会被优化为一次调用。

除此之外，把函数内容放到结果集部分，两种状态的差异也很大，如下测试可以看出：

MogDB=# create table test_stable (id int, info text);  


CREATE TABLE


MogDB=# insert into test_stable select generate_series(1,100000),random()::text;  


INSERT 0 1000


MogDB=# create index idx_test_a on test(id);  


CREATE INDEX

创建如下函数：

create or replace function func_stable() returns numeric as $$  


declare  


begin  


  return 2;  


end;  


$$ language plpgsql stable;

使用如下语句可以进行验证两种方式的执行计划时间明显不同：

explain analyze select func_stable() from test_stable;

因此，在事务中参数一致则结果一致，且只关注函数的最终结果可以标记为 IMMUTABLE，因为它调用函数的次数少，固化了函数的结果，执行计划的代价较小。但是一般还是建议使用 STABLE，因为它会老老实实地去执行函数，而不是只取一个固化的结果。

03

VOLATILE(不稳定)

默认为 VOLATILE 。表示该函数的值在一次表扫描中都有可能改变，因此不能做优化。

VOLATILE函数可以做任何事情，包括修改数据库状态。在连续调用时即使使用相同的参数，也可能会返回不同的结果。优化器不会优化掉此类函数，每次调用都会重新求值。

在这种意义上，相对较少的数据库函数是不稳定的。例如 : random(), currval(), timeofday()。任何具有副作用的函数都不稳定的，即使其结果是可预测的。例如 : setval()。

只有函数声明了 VOLATILE状态才可以进行 update操作。

具体的验证如下所示：

MogDB=# create table test_volatile (id int, info text);  


CREATE TABLE


MogDB=# insert into test_volatile select generate_series(1,1000),random()::text;  


INSERT 0 1000


MogDB=# create index idx_test on test(id);  


CREATE INDEX

然后建立测试的函数：

create or replace function func_volatile(ida int) returns text as $$  


declare  


  result text;  


begin   


  update test_volatile set info='xiaoguaishou' where id=ida returning info into result;  


  return result;  


end;


$$ language plpgsql volatile;

可以看到测试结果：

“

作者简介

/ Author Introduction

阎书利 / 云和恩墨PG技术顾问

PostgreSQL ACE，《快速掌握 PostgreSQL 版本新特性》一书副主编，中国PG分会认证讲师，PGfans 2021年度MVP，Gauss松鼠会2021年度优秀会员，拥有PGCM、OCP(MySQL)等十多项数据库认证，目前主要从事于 PostgreSQL、openGauss / MogDB 的运维以及去O工作。

ITPub博客

循序渐进丨MogDB 数据库的三种函数稳定性关键字

最新文章