函数稳定性讲解 - retalk PostgreSQL function’s [ volatile stable immutable ]

背景

PostgreSQL 函数的稳定性, 以前写过几篇BLOG讲述, 见参考部分.

本文再细化并举例说明一下他们的差别.

首先函数稳定性分三种 :

volatile  
stable  
immutable  

首先创建1个测试表 :

digoal=> create table test (id int, info text);  
CREATE TABLE  
digoal=> insert into test select generate_series(1,100000),random()::text;  
INSERT 0 100000  
digoal=> create index idx_test_1 on test(id);  
CREATE INDEX  

1. volatile指函数可以修改数据库, 函数参数值相同的情况下, 可以返回不同的结果, 所以volatile函数在执行过程中优化器对它的处理是每一行都需要执行一次volatile函数.

例如 :

create or replace function f_volatile(i_id int) returns text as $$  
declare  
  result text;  
begin  
  -- update可以用在volatile函数中, 因为UPDATE要修改数据  
  update test set info='new' where id=i_id returning info into result;  
  return result;  
end;  
$$ language plpgsql volatile;  

执行这个函数, 正常返回 :

如果是immutable或者stable的话, 将报错.

digoal=> select * from f_volatile(1);  
 f_volatile   
------------  
 new  
(1 row)  

下面的函数用来返回一个NUMERIC, 然后进行sum运算.

create or replace function f_test() returns numeric as $$  
declare  
begin  
  return 1.5;  
end;  
$$ language plpgsql volatile;  

10W条记录, 执行f_test()耗时335毫秒.

digoal=> explain analyze select f_test() from test;  
                                                  QUERY PLAN                                                    
--------------------------------------------------------------------------------------------------------------  
 Seq Scan on test  (cost=0.00..26638.00 rows=100000 width=0) (actual time=0.035..322.622 rows=100000 loops=1)  
 Total runtime: 334.539 ms  
(2 rows)  
Time: 335.035 ms  

记住这个执行耗时. 后面要对比f_test()改成stable和immutable后的执行耗时.

单条执行时间 :

digoal=> select f_test();  
 f_test   
--------  
    1.5  
(1 row)  
Time: 0.192 ms  

2. stable 函数, 不允许修改数据库.

如下 :

digoal=> alter function f_volatile(int) stable;  
ALTER FUNCTION  
Time: 0.660 ms  

再次执行f_volatile将报错, 因为stable的函数不允许执行修改数据库的SQL, 例如UPDATE.

digoal=> select * from f_volatile(1);  
ERROR:  UPDATE is not allowed in a non-volatile function  
CONTEXT:  SQL statement "update test set info='new' where id=i_id returning info"  
PL/pgSQL function f_volatile(integer) line 5 at SQL statement  
Time: 0.869 ms  

同样的参数值, stable函数多次执行返回的结果应该一致.

因此优化器可以选择将多次调用stable函数改为一次调用. stable函数作为where条件中的比较值是, 可以使用索引. 因为走索引需要一个常量.

digoal=> alter function f_test() stable;  
ALTER FUNCTION  
digoal=> explain analyze select * from test where id<=f_test()::int;  
                                                    QUERY PLAN                                                      
------------------------------------------------------------------------------------------------------------------  
 Index Scan using idx_test_1 on test  (cost=0.25..2.55 rows=1 width=21) (actual time=0.019..0.024 rows=2 loops=1)  
   Index Cond: (id <= (f_test())::integer)  
 Total runtime: 0.054 ms  
(3 rows)  
Time: 0.926 ms  

改回volatile, 则不允许走索引. 如下 :

digoal=> explain analyze select * from test where id<=f_test()::int;  
                                               QUERY PLAN                                                  
---------------------------------------------------------------------------------------------------------  
 Seq Scan on test  (cost=0.00..27138.00 rows=33333 width=21) (actual time=0.143..269.208 rows=2 loops=1)  
   Filter: (id <= (f_test())::integer)  
   Rows Removed by Filter: 99998  
 Total runtime: 269.242 ms  
(4 rows)  
Time: 269.937 ms  

另外一个测试是吧f_test()放到结果集部分, 而不是where条件里面, stable和immutable的差别也很大 :

digoal=> alter function f_test() stable;  
ALTER FUNCTION  
digoal=> explain analyze select f_test() from test;  
                                                  QUERY PLAN                                                    
--------------------------------------------------------------------------------------------------------------  
 Seq Scan on test  (cost=0.00..26638.00 rows=100000 width=0) (actual time=0.137..268.707 rows=100000 loops=1)  
 Total runtime: 281.684 ms  
(2 rows)  
Time: 282.248 ms  

改成immutable

digoal=> alter function f_test() immutable;  
ALTER FUNCTION  
Time: 0.359 ms  
digoal=> explain analyze select f_test() from test;  
                                                 QUERY PLAN                                                   
------------------------------------------------------------------------------------------------------------  
 Seq Scan on test  (cost=0.00..1638.00 rows=100000 width=0) (actual time=0.011..23.450 rows=100000 loops=1)  
 Total runtime: 34.331 ms  
(2 rows)  
Time: 35.061 ms  

3. immutable, 和stable非常类似, 但是immutable是指在任何情况下, 只要参数一致, 结果就一致. 而在事务中参数一致则结果一致可以标记为stable而请你不要把它标记为immutable.

另外的显著的区别是优化器对immutable和stable函数的处理上.

如果函数的参数是常量的情况下 :

immutable函数在优化器生成执行计划时会将函数结果替换函数. 也就是函数不在输出的执行计划中, 取而代之的是一个结果常量.

stable函数则不会如此, 执行计划输出后还是函数.

例如 :

select * from test where id> 1+2;  
  
+对应的操作符函数是immutable的, 所以这条SQL执行计划输出的是 :   
  
select * from test where id>3;  

对于用户自己创建的函数也是如此 :

digoal=> create or replace function f_test(i_id int) returns int as $$  
declare  
begin  
  return i_id;  
end;  
$$ language plpgsql immutable;  
CREATE FUNCTION  
Time: 1.020 ms  

immutable 测试 :

digoal=> explain analyze select * from test where id<f_test(50);  
                                                     QUERY PLAN                                                       
--------------------------------------------------------------------------------------------------------------------  
 Index Scan using idx_test_1 on test  (cost=0.00..3.15 rows=50 width=21) (actual time=0.007..0.025 rows=49 loops=1)  
   Index Cond: (id < 50)  
 Total runtime: 0.058 ms  
(3 rows)  

注意这行 :

   Index Cond: (id < 50), f_test(50)已经替换成了结果50.  

stable 测试 :

digoal=> alter function f_test(int) stable;  
ALTER FUNCTION  
digoal=> explain analyze select * from test where id<f_test(50);  
                                                     QUERY PLAN                                                       
--------------------------------------------------------------------------------------------------------------------  
 Index Scan using idx_test_1 on test  (cost=0.25..3.40 rows=50 width=21) (actual time=0.019..0.035 rows=49 loops=1)  
   Index Cond: (id < f_test(50))  
 Total runtime: 0.066 ms  
(3 rows)  

注意这行 :

   Index Cond: (id < f_test(50)), f_test(50)没有被替换掉.  

另外一组测试 :

digoal=> alter function f_test(int) stable;  
ALTER FUNCTION  
digoal=> explain analyze select * from test where f_test(2)>1;  
                                                    QUERY PLAN                                                       
-------------------------------------------------------------------------------------------------------------------  
 Result  (cost=0.25..1638.25 rows=100000 width=21) (actual time=0.146..50.367 rows=100000 loops=1)  
   One-Time Filter: (f_test(2) > 1)  
   ->  Seq Scan on test  (cost=0.25..1638.25 rows=100000 width=21) (actual time=0.014..20.646 rows=100000 loops=1)  
 Total runtime: 61.386 ms  
(4 rows)  

当f_test是stable 时, 比immutable多One-Time Filter: (f_test(2) > 1)

而当immutable, 优化器则将f_test(2)>1这部分直接优化掉了.

digoal=> alter function f_test(int) immutable;  
ALTER FUNCTION  
digoal=> explain analyze select * from test where f_test(2)>1;  
                                                 QUERY PLAN                                                    
-------------------------------------------------------------------------------------------------------------  
 Seq Scan on test  (cost=0.00..1638.00 rows=100000 width=21) (actual time=0.011..18.801 rows=100000 loops=1)  
 Total runtime: 29.839 ms  
(2 rows)  

prepare statement 注意

prepare statement请参考 :

[《执行计划选择算法 与 绑定变量 - PostgreSQL prepared statement: SPI_prepare, prepare

execute COMMAND, PL/pgsql STYLE: custom & generic plan cache》](../201212/20121224_01.md)

这里需要注意的是immutable函数, 如果你的函数实际上不是immutable的. 但是你把它标记为immutable了, 可能有意想不到的结果 :

digoal=> create or replace function immutable_random() returns numeric as $$  
declare  
begin  
return random();  
end;  
$$ language plpgsql immutable;  
CREATE FUNCTION  

创建一个prepare statement.

digoal=> prepare p_test(int) as select $1,immutable_random();  
PREPARE  
Time: 0.473 ms  

执行这个prepared statement :

digoal=> execute p_test(1);  
 ?column? | immutable_random    
----------+-------------------  
        1 | 0.277766926214099  
(1 row)  
Time: 0.398 ms  
digoal=> execute p_test(1);  
 ?column? | immutable_random    
----------+-------------------  
        1 | 0.974089733790606  
(1 row)  
Time: 0.209 ms  
digoal=> execute p_test(1);  
 ?column? | immutable_random    
----------+-------------------  
        1 | 0.800415104720742  
(1 row)  
Time: 0.212 ms  
digoal=>   
digoal=> execute p_test(1);  
 ?column? | immutable_random   
----------+------------------  
        1 | 0.41237005777657  
(1 row)  
Time: 0.290 ms  
digoal=> execute p_test(1);  
 ?column? |  immutable_random    
----------+--------------------  
        1 | 0.0541226323693991  
(1 row)  
Time: 0.211 ms  

第六次开始使用generic_plan, 而immutable function在plan时将被结果常量替换.

digoal=> execute p_test(1);  
 ?column? | immutable_random    
----------+-------------------  
        1 | 0.431490630842745  
(1 row)  
Time: 0.233 ms  

以后再执行这个prepare statement, immutable_random()部分都将得到同样的结果.

digoal=> execute p_test(1);  
 ?column? | immutable_random    
----------+-------------------  
        1 | 0.431490630842745  
(1 row)  
Time: 0.165 ms  
digoal=> execute p_test(2);  
 ?column? | immutable_random    
----------+-------------------  
        2 | 0.431490630842745  
(1 row)  
Time: 0.273 ms  
digoal=> execute p_test(3);  
 ?column? | immutable_random    
----------+-------------------  
        3 | 0.431490630842745  
(1 row)  
Time: 0.149 ms  

而把immutable_random()改成volatile或者stable后, immutable_random()都将产生不同的结果, 不会发生以上情况.

因为他们在plan时函数不会被结果替换.

所以在prepare statement中使用immutable函数, 需要特别注意这个函数到底是不是真的是immutable的.

MVCC 注意

这里要注意的是volatile, stable, immutable这几种函数, 对数据的修改的可见性分两种情况.

volatile , 调用该函数的SQL对数据的修改, 可见.

stable, immutable , 调用该函数的SQL对数据的修改, 不可见.

 STABLE and IMMUTABLE functions use a snapshot established as of the start of the calling query,   
 whereas VOLATILE functions obtain a fresh snapshot at the start of each query they execute.  

例如 :

创建测试表 :

digoal=> create table test(id int,info text);  
CREATE TABLE  
Time: 50.356 ms  
digoal=> insert into test select 1,random()::text from generate_series(1,1000);  
INSERT 0 1000  
Time: 5.027 ms  

创建修改函数, 这个函数将在另一个函数中调用, 用来修改ID。

因为另一个函数是用perform f_mod(int)来修改数据, 所以另外一个函数可以改成volatile, stable, immutable任意.

digoal=> create or replace function f_mod(i_id int) returns void as $$     
declare  
begin           
  update test set id=i_id+1 where id=i_id;  
end;                    
$$ language plpgsql volatile;  

测试稳定性的函数 :

digoal=> create or replace function f_test(i_id int) returns bigint as $$  
declare  
  result int8;  
begin  
  perform f_mod(i_id);  
  select count(*) into result from test where id=i_id;  
  return result;  
end;  
$$ language plpgsql volatile;  

当稳定性=volatile时, 修改可以被select count(*) into result from test where id=i_id; 看到 :

所以更新后结果为0 :

digoal=> select f_test(1);  
 f_test   
--------  
      0  
(1 row)  

改成stable, 它看到的是SQL开始是的snapshot, 所以对修改不可见, 结果还是1000 :

digoal=> alter function f_test(int) stable;  
ALTER FUNCTION  
digoal=> select f_test(2);  
 f_test   
--------  
   1000  
(1 row)  

改成immutable, 它看到的是SQL开始是的snapshot, 所以对修改不可见, 结果还是1000 :

digoal=> alter function f_test(int) immutable;  
ALTER FUNCTION  
digoal=> select f_test(3);  
 f_test   
--------  
   1000  
(1 row)  

还有一种情况是如果修改是来自函数体外部的修改, 那是否可见?

digoal=> create or replace function f_test(i_id int) returns bigint as $$  
declare  
  result int8;  
begin  
  select count(*) into result from test where id=i_id;    
  return result;  
end;                                                                    
$$ language plpgsql volatile;  
CREATE FUNCTION  

看不到with的修改 :

digoal=> alter function f_test(int) immutable;  
ALTER FUNCTION  
digoal=> with t1 as (  
digoal(>   update test set id=id+1 where id=4  
digoal(> )  
digoal-> select f_test(4);  
 f_test   
--------  
   1000  
(1 row)  

看不到with的修改 :

digoal=> alter function f_test(int) stable;  
ALTER FUNCTION  
digoal=> with t1 as (  
  update test set id=id+1 where id=5  
)  
select f_test(5);  
 f_test   
--------  
   1000  
(1 row)  

看不到with的修改 :

digoal=> alter function f_test(int) volatile;  
ALTER FUNCTION  
digoal=> with t1 as (                          
  update test set id=id+1 where id=6  
)  
select f_test(6);  
 f_test   
--------  
   1000  
(1 row)  

在事务中时, 都能看到本事务在前面做的修改 :

digoal=> alter function f_test(int) immutable;  
ALTER FUNCTION  
digoal=> begin;  
BEGIN  
digoal=> update test set id=id+1 where id=13;  
UPDATE 1000  
digoal=> select f_test(13);  
 f_test   
--------  
      0  
(1 row)  
digoal=> select f_test(14);  
 f_test   
--------  
   1000  
(1 row)  
digoal=> end;  
COMMIT  
volatile, stable测试略, 同上。  

其他

1. 查看函数的稳定性 :

digoal=> select proname,proargtypes,provolatile from pg_proc where proname='f_test';  
 proname | proargtypes | provolatile   
---------+-------------+-------------  
 f_test  |             | i  
 f_test  | 23          | i  
(2 rows)  

i表示immutable, s表示stable, v表示volatile.

2. 请按实际情况严格来标记一个函数的稳定性.

3. stable函数和immutable函数不能直接调用UPDATE这种修改数据库的SQL语句. 但是通过perform volatile function或者select volatile function还是会修改到数据, 因为PostgreSQL不会有更深层次的检查.

参考

1. http://www.postgresql.org/docs/9.2/static/spi-spi-prepare.html

2. http://www.postgresql.org/docs/9.2/static/plpgsql-implementation.html

3. http://www.postgresql.org/docs/9.2/static/xfunc-volatility.html

4. 《函数稳定性讲解 - Thinking PostgreSQL Function’s Volatility Categories》

5. 《函数稳定性讲解 - 函数索引思考, pay attention to function index used in PostgreSQL》

6. http://www.postgresql.org/docs/9.2/static/monitoring-stats.html

7. [《执行计划选择算法 与 绑定变量 - PostgreSQL prepared statement: SPI_prepare, prepare

execute COMMAND, PL/pgsql STYLE: custom & generic plan cache》](../201212/20121224_01.md)

8. http://www.postgresql.org/docs/9.2/static/sql-createfunction.html

digoal’s 大量PostgreSQL文章入口