PostgreSQL中函数的三态

112 阅读 0 评论 74 点赞

我是靠谱客的博主壮观音响，这篇文章主要介绍PostgreSQL中函数的三态，现在分享给大家，希望可以做个参考。

postgresql中每一个函数都有一个易变性分类，可能是 VOLATILE、STABLE或者IMMUTABLE。如果CREATEFUNCTION命令没有指定一个分类，则默认是VOLATILE。

VOLATILE函数可以做任何事情，包括修改数据库。在使用相同的参数连续调用时，它能返回不同的结果

STABLE函数不能修改数据库并且被确保对一个语句中的所有行用给定的相同参数返回相同的结果

IMMUTABLE函数不能修改数据库并且被确保用相同的参数永远返回相同的结果

我们先来看看vlolatile和stable函数的区别,这两者其实比较好区分:简单来说,使用volatile函数在同一个事务中即使是相同的参数,返回的结果也会不同,而stable函数在同一个事务中多次调用返回的值是固定的,比如说我们常见的now和clock_timestamp函数,这两个函数都是获取当前系统的时间,但是now是stable类型的函数,clock_timestamp是volatile类型的.

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
bill=# /df+ now
                                                                                        List of functions
   Schema   | Name |     Result data type     | Argument data types | Type | Volatility | Parallel |  Owner   | Security | Access privileges | Language | Source code |       Description        
------------+------+--------------------------+---------------------+------+------------+----------+----------+----------+-------------------+----------+-------------+--------------------------
 pg_catalog | now  | timestamp with time zone |                     | func | stable     | safe     | postgres | invoker  |                   | internal | now         | current transaction time
(1 row)

bill=# /df+ clock_timestamp
                                                                                            List of functions
   Schema   |      Name       |     Result data type     | Argument data types | Type | Volatility | Parallel |  Owner   | Security | Access privileges | Language |   Source code   |    Description     
------------+-----------------+--------------------------+---------------------+------+------------+----------+----------+----------+-------------------+----------+-----------------+--------------------
 pg_catalog | clock_timestamp | timestamp with time zone |                     | func | volatile   | safe     | postgres | invoker  |                   | internal | clock_timestamp | current clock time
(1 row)

因此我们在同一个事务中调用这两个函数的值是不一样的!
now:

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
bill=# begin;
BEGIN
bill=# select now();
              now              
-------------------------------
 2019-09-05 16:56:17.300841+08
(1 row)

bill=# select now();
              now              
-------------------------------
 2019-09-05 16:56:17.300841+08
(1 row)

bill=# select now();
              now              
-------------------------------
 2019-09-05 16:56:17.300841+08
(1 row)

bill=# end;
COMMIT

clock_timestamp:

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
bill=# begin;
BEGIN
bill=# select clock_timestamp();
        clock_timestamp        
-------------------------------
 2019-09-05 16:56:55.789889+08
(1 row)

bill=# select clock_timestamp();
        clock_timestamp        
-------------------------------
 2019-09-05 16:56:56.629734+08
(1 row)

bill=# select clock_timestamp();
        clock_timestamp        
-------------------------------
 2019-09-05 16:56:57.299359+08
(1 row)

可以看到,在同一个事务中now函数无论调用多少次返回的结果都是一样的,而clock_timestamp函数返回的结果并不一样.

接着我们来看下stable和immutable函数的区别,这两者之间区别相对比较小.一般来说:stable函数在一个语句中给定相同参数返回相同的结果。但是immutable函数却是只要给定相同参数,永远返回相同的结果.所以immutable类型的函数可以用来创建函数索引,而stable函数却不可以.
例子:
创建stable函数:

复制代码

1
2
3
4
5
6
7
8
9
10
11
bill=# create function f_stable(c1 int,c2 int) returns int as $$
bill$# declare
bill$# result int;
bill$# begin 
bill$# select c1+c2 into result;
bill$# return result;
bill$# end;
bill$# $$
bill-# language plpgsql stable;
CREATE FUNCTION

创建immutable函数:

复制代码

1
2
3
4
5
6
7
8
9
10
11
bill=# create function f_immutable(c1 int,c2 int) returns int as $$
bill$# declare
bill$# result int;
bill$# begin 
bill$# select c1+c2 into result;
bill$# return result;
bill$# end;
bill$# $$
bill-# language plpgsql immutable;
CREATE FUNCTION

创建函数索引:

复制代码

1
2
3
bill=# create index idx_t1 on t1 using btree(f_stable(c1,c2));
psql: ERROR:  functions in index expression must be marked IMMUTABLE

当使用stable函数时会报错.

复制代码

1
2
3
bill=# create index idx_t1 on t1 using btree(f_immutable(c1,c2));
CREATE INDEX

创建成功,我们再看看该索引能否正常使用呢?

复制代码

1
2
3
4
5
6
7
bill=# explain select * from t1 where f_immutable(c1,c2) = 100;
                            QUERY PLAN                             
-------------------------------------------------------------------
 Index Scan using idx_t1 on t1  (cost=0.15..12.05 rows=11 width=8)
   Index Cond: (f_immutable(c1, c2) = 100)
(2 rows)

查询的确可以走索引!

通过以上实验我们可以得到结论:
volatile函数在同一个事务中即使是相同的参数,返回的结果也会不同;
stable函数在同一个事务中对于相同的参数,返回的结果也会相同;
immutable函数却是只要给定相同参数,永远返回相同的结果.