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(@H_404_86@1,@H_404_86@100000),random()::text; 
   
   
    
    INSERT @H_404_86@0 @H_404_86@100000 
   
   
    
    @H_404_86@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(@H_404_86@1);
  
  
  
  
   
   f_volatile
  
  
  
  
   
   ------------
  
  
  
  
   
   new
  
  
  
  
   
   (@H_404_86@1 row)

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

  
  
   
   create or replace function f_test() returns numeric as $$
  
  
  
  
   
   declare
  
  
  
  
   
   begin
  
  
  
  
   
    return @H_404_86@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=@H_404_86@0.00..@H_404_86@26638.00 rows=@H_404_86@100000 width=@H_404_86@0) (actual time=@H_404_86@0.035..@H_404_86@322.622 rows=@H_404_86@100000 loops=@H_404_86@1)  Total runtime: @H_404_86@334.539 ms (@H_404_86@2 rows) Time: @H_404_86@335.035 ms

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

单条执行时间 :

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

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

如下 :

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

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

   
   
    
    digoal=> select * from f_volatile(@H_404_86@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 @H_404_86@5 at sql statement
   
   
   
   
    
    Time: @H_404_86@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=@H_404_86@0.25..@H_404_86@2.55 rows=@H_404_86@1 width=@H_404_86@21) (actual time=@H_404_86@0.019..@H_404_86@0.024 rows=@H_404_86@2 loops=@H_404_86@1) 
   
   
    
     Index Cond: (id <= (f_test())::integer) 
   
   
    
    Total runtime: @H_404_86@0.054 ms 
   
   
    
    (@H_404_86@3 rows) 
   
   
    
    Time: @H_404_86@0.926 ms

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

  
  
   
   digoal=> explain analyze select * from test where id<=f_test()::int;
  
  
  
  
   
                          QUERY PLAN                        
  
  
  
  
   
   ---------------------------------------------------------------------------------------------------------
  
  
  
  
   
   Seq Scan on test (cost=@H_404_86@0.00..@H_404_86@27138.00 rows=@H_404_86@33333 width=@H_404_86@21) (actual time=@H_404_86@0.143..@H_404_86@269.208 rows=@H_404_86@2 loops=@H_404_86@1)
  
  
  
  
   
    Filter: (id <= (f_test())::integer)
  
  
  
  
   
    Rows Removed by Filter: @H_404_86@99998
  
  
  
  
   
   Total runtime: @H_404_86@269.242 ms
  
  
  
  
   
   (@H_404_86@4 rows)
  
  
  
  
   
   Time: @H_404_86@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=@H_404_86@0.00..@H_404_86@26638.00 rows=@H_404_86@100000 width=@H_404_86@0) (actual time=@H_404_86@0.137..@H_404_86@268.707 rows=@H_404_86@100000 loops=@H_404_86@1) 
    
    
     
     Total runtime: @H_404_86@281.684 ms 
    
    
     
     (@H_404_86@2 rows) 
    
    
     
     Time: @H_404_86@282.248 ms 
    
   
   
   
   
    
     
   
   
   
   
    
    改成immutable
   
   
   
   
    
     
     digoal=> alter function f_test() immutable; 
    
    
     
     ALTER FUNCTION 
    
    
     
     Time: @H_404_86@0.359 ms 
    
    
     
     digoal=> explain analyze select f_test() from test; 
    
    
     
                             QUERY PLAN                         
    
    
     
     ------------------------------------------------------------------------------------------------------------ 
    
    
     
     Seq Scan on test (cost=@H_404_86@0.00..@H_404_86@1638.00 rows=@H_404_86@100000 width=@H_404_86@0) (actual time=@H_404_86@0.011..@H_404_86@23.450 rows=@H_404_86@100000 loops=@H_404_86@1) 
    
    
     
     Total runtime: @H_404_86@34.331 ms 
    
    
     
     (@H_404_86@2 rows) 
    
    
     
     Time: @H_404_86@35.061 ms

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

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

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

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

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

例如 :

select * from test where id> @H_404_86@1+@H_404_86@2;

+对应的操作符函数是immutable的,所以这条sql执行计划输出的是 :

select * from test where id>@H_404_86@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: @H_404_86@1.020 ms

immutable 测试 :

  
  
   
    
    digoal=> explain analyze select * from test where id<f_test(@H_404_86@50); 
   
   
    
                              QUERY PLAN                           
   
   
    
    -------------------------------------------------------------------------------------------------------------------- 
   
   
    
    Index Scan using idx_test_1 on test (cost=@H_404_86@0.00..@H_404_86@3.15 rows=@H_404_86@50 width=@H_404_86@21) (actual time=@H_404_86@0.007..@H_404_86@0.025 rows=@H_404_86@49 loops=@H_404_86@1) 
   
   
    
     Index Cond: (id < @H_404_86@50) 
   
   
    
    Total runtime: @H_404_86@0.058 ms 
   
   
    
    (@H_404_86@3 rows) 
   
  
  
  
  
   
   注意这行 :
  
  
  
  
   
    Index Cond: (id < @H_404_86@50), f_test(@H_404_86@50)已经替换成了结果@H_404_86@50.

stable 测试 :

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

另外一组测试 :

   
   
    
    digoal=> alter function f_test(int) stable;
   
   
   
   
    
    ALTER FUNCTION
   
   
   
   
    
    digoal=> explain analyze select * from test where f_test(@H_404_86@2)>@H_404_86@1;
   
   
   
   
    
                              QUERY PLAN                          
   
   
   
   
    
    -------------------------------------------------------------------------------------------------------------------
   
   
   
   
    
    Result (cost=@H_404_86@0.25..@H_404_86@1638.25 rows=@H_404_86@100000 width=@H_404_86@21) (actual time=@H_404_86@0.146..@H_404_86@50.367 rows=@H_404_86@100000 loops=@H_404_86@1)
   
   
   
   
    
     One-Time Filter: (f_test(@H_404_86@2) > @H_404_86@1)
   
   
   
   
    
     -> Seq Scan on test (cost=@H_404_86@0.25..@H_404_86@1638.25 rows=@H_404_86@100000 width=@H_404_86@21) (actual time=@H_404_86@0.014..@H_404_86@20.646 rows=@H_404_86@100000 loops=@H_404_86@1)
   
   
   
   
    
    Total runtime: @H_404_86@61.386 ms
   
   
   
   
    
    (@H_404_86@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(@H_404_86@2)>@H_404_86@1;
   
   
   
   
    
                            QUERY PLAN                         
   
   
   
   
    
    -------------------------------------------------------------------------------------------------------------
   
   
   
   
    
    Seq Scan on test (cost=@H_404_86@0.00..@H_404_86@1638.00 rows=@H_404_86@100000 width=@H_404_86@21) (actual time=@H_404_86@0.011..@H_404_86@18.801 rows=@H_404_86@100000 loops=@H_404_86@1)
   
   
   
   
    
    Total runtime: @H_404_86@29.839 ms
   
   
   
   
    
    (@H_404_86@2 rows)

【prepare statement 注意】

prepare statement请参考 :

http://blog.163.com/digoal@126/blog/static/1638770402012112452432251/

这里需要注意的是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: @H_404_86@0.473 ms

执行这个prepared statement :

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

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

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

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

   
   
    
    digoal=> execute p_test(@H_404_86@1);
   
   
   
   
    
    ?column? | immutable_random 
   
   
   
   
    
    ----------+-------------------
   
   
   
   
    
        @H_404_86@1 | @H_404_86@0.431490630842745
   
   
   
   
    
    (@H_404_86@1 row)
   
   
   
   
    
    Time: @H_404_86@0.165 ms
   
   
   
   
    
     
     digoal=> execute p_test(@H_404_86@2); 
    
    
     
     ?column? | immutable_random  
    
    
     
     ----------+------------------- 
    
    
     
         @H_404_86@2 | @H_404_86@0.431490630842745 
    
    
     
     (@H_404_86@1 row) 
    
    
     
     Time: @H_404_86@0.273 ms 
    
    
     
     digoal=> execute p_test(@H_404_86@3); 
    
    
     
     ?column? | immutable_random  
    
    
     
     ----------+------------------- 
    
    
     
         @H_404_86@3 | @H_404_86@0.431490630842745 
    
    
     
     (@H_404_86@1 row) 
    
    
     
     Time: @H_404_86@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: @H_404_86@50.356 ms
  
  
  
  
   
   digoal=> insert into test select @H_404_86@1,random()::text from generate_series(@H_404_86@1,@H_404_86@1000);
  
  
  
  
   
   INSERT @H_404_86@0 @H_404_86@1000
  
  
  
  
   
   @H_404_86@Time: @H_404_86@5.027 ms

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

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

  
  
   
   digoal=> create or replace function f_mod(i_id int) returns void as $$ 
  
  
  
  
   
   declare
  
  
  
  
   
   begin    
  
  
  
  
   
    update test set id=i_id+@H_404_86@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(@H_404_86@1);
  
  
  
  
   
   f_test
  
  
  
  
   
   --------
  
  
  
  
   
      @H_404_86@0
  
  
  
  
   
   (@H_404_86@1 row)

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

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

改成immutable,结果还是1000 :

   
   
    
    digoal=> alter function f_test(int) immutable;
   
   
   
   
    
    ALTER FUNCTION
   
   
   
   
    
    digoal=> select f_test(@H_404_86@3);
   
   
   
   
    
    f_test
   
   
   
   
    
    --------
   
   
   
   
    
     @H_404_86@1000
   
   
   
   
    
    (@H_404_86@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+@H_404_86@1 where id=@H_404_86@4 
   
   
    
    @H_404_86@digoal(> ) 
   
   
    
    digoal-> select f_test(@H_404_86@4); 
   
   
    
    f_test 
   
   
    
    -------- 
   
   
    
     @H_404_86@1000 
   
   
    
    (@H_404_86@1 row)

看不到with的修改 :

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

看不到with的修改 :

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

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

  
  
   
   digoal=> alter function f_test(int) immutable;
  
  
  
  
   
   ALTER FUNCTION
  
  
  
  
   
   digoal=> begin;
  
  
  
  
   
   BEGIN
  
  
  
  
   
   digoal=> update test set id=id+@H_404_86@1 where id=@H_404_86@13;
  
  
  
  
   
   UPDATE @H_404_86@1000
  
  
  
  
   
   @H_404_86@digoal=> select f_test(@H_404_86@13);
  
  
  
  
   
   f_test
  
  
  
  
   
   --------
  
  
  
  
   
      @H_404_86@0
  
  
  
  
   
   (@H_404_86@1 row)
  
  
  
  
   
   digoal=> select f_test(@H_404_86@14);
  
  
  
  
   
   f_test
  
  
  
  
   
   --------
  
  
  
  
   
    @H_404_86@1000
  
  
  
  
   
   (@H_404_86@1 row)
  
  
  
  
   
   digoal=> end;
  
  
  
  
   
   COMMIT