PostgreSQL 函数稳定性状态

PostgreSQL 函数在定义的时候有三个稳定性状态可选:

IMMUTABLE | STABLE | VOLATILE

分别代表，非常稳定，稳定，不稳定。

不稳定，函数可以修改数据库的数据，输入同样的参数可以返回不同的结果，同一个QUERY中，如果需要返回该函数的结果，那么每一行都会运算一遍这个函数(后面会有例子)。

稳定，函数不可以修改数据库的数据，同一个QUERY中，如果需要返回该函数的结果，那么将合并多次运算为一次这个函数(后面会有例子)。另外，只有stable和immutable的函数才可以被执行计划选择作为索引的比较条件。(因为索引比较时，被比较的值只运算一次.这个就需要stable和immutable了)

非常稳定,函数不可以修改数据库的数据,并且在任何情况下调用，只要输入参数一致，返回结果都一致。

在创建函数时，必须严格的定义稳定性状态，否则可能导致意想不到的后果，因为PLAN CACHE以及prepared statement等原因.

函数索引必须是immutable的 . 

注意稳定和非常稳定的函数中只能出现SELECT语句。但是SELECT语句中可以调用不稳定函数，因此这些稳定性选项都不是强限制。

另外稳定性选项还影响了对数据的可视特性,如  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.

实例:

下面来用几个时间函数来测试一下:

   
   

    
                    proname                 | provolatile | pronargs 
   
   
   
   

    
    ----------------------------------------+-------------+----------
   
   
   
   

    
     timenow                                | s           |        0
   
   
   
   

    
     timeofday                              | v           |        0
   
   
   
   

    
     now                                    | s           |        0
   
   
   
   

    
     transaction_timestamp                  | s           |        0
   
   
   
   

    
     statement_timestamp                    | s           |        0
   
   
   
   

    
     clock_timestamp                        | v           |        0
   
   

其中

clock_timestamp是voatile的.now是stable的。

  
  

   
   
   
   

    
    digoal
    
    =>
    
     create table tbl_time 
    
    (
    
    id 
    
    int
    
    ,
    
    row_time timestamp without time zone
    
    ,
    
    stat_time timestamp without time zone
    
    );
   
   
   
   

    
    CREATE 
    
    TABLE
   
   
   
   

    
    digoal
    
    =>
    
     insert 
    
    into
    
     tbl_time 
    
    select
    
     generate_series
    
    (
    
    1
    
    ,
    
    10000
    
    ),
    
    clock_timestamp
    
    (),
    
    now
    
    ();
   
   
   
   

    
    INSERT 
    
    0
    
     
    
    10000
   
   
   
   


   
   
  
  
  
  

   
   

    
    digoal=> select count(*),count(distinct row_time),count(distinct stat_time) from tbl_time;
   
   
   
   

    
     count | count | count 
   
   
   
   

    
    -------+-------+-------
   
   
   
   

    
     10000 | 10000 |     1
   
   
   
   

    
    (1 row)
   
   
  
  

# 情况已经很明朗了

volatile每一行都运算了，stable的只是STATEMANT开始是运算。

# 再来看看索引的比较

  
  

   
   digoal=> explain select * from tbl_time where row_time>now();
  
  
  
  

   
                                     QUERY PLAN                                  
  
  
  
  

   
   ------------------------------------------------------------------------------
  
  
  
  

   
    Index Scan using idx_row_time on tbl_time  (cost=0.00..4.27 rows=1 width=20)
  
  
  
  

   
      Index Cond: (row_time > now())
  
  
  
  

   
   (2 rows)
  
  
  
  

   
   

  
  
  
  

   
   digoal=> explain select * from tbl_time where row_time>clock_timestamp();
  
  
  
  

   
                             QUERY PLAN                          
  
  
  
  

   
   --------------------------------------------------------------
  
  
  
  

   
    Seq Scan on tbl_time  (cost=0.00..214.00 rows=3333 width=20)
  
  
  
  

   
      Filter: (row_time > clock_timestamp())
  
  
  
  

   
   (2 rows)
  
  

# 很明显,volatile的函数在WHERE条件中，不走索引。而now()即stable的函数，使用了索引。

# 把clock_timestamp改成stable试试。马上就走索引了。不过这个不能乱改.

  
  

   
   digoal=> \c digoal postgres
  
  
  
  

   
   You are now connected to database "digoal" as user "postgres".
  
  
  
  

   
   digoal=# alter function clock_timestamp() strict stable;
  
  
  
  

   
   ALTER FUNCTION
  
  
  
  

   
   digoal=# \c digoal digoal
  
  
  
  

   
   You are now connected to database "digoal" as user "digoal".
  
  
  
  

   
   digoal=> explain select * from tbl_time where row_time>clock_timestamp();
  
  
  
  

   
                                     QUERY PLAN                                  
  
  
  
  

   
   ------------------------------------------------------------------------------
  
  
  
  

   
    Index Scan using idx_row_time on tbl_time  (cost=0.00..4.27 rows=1 width=20)
  
  
  
  

   
      Index Cond: (row_time > clock_timestamp())
  
  
  
  

   
   (2 rows)
  
  

# 那么看看插入会不会受到影响

   
   

    
    digoal=> truncate table tbl_time;
   
   
   
   

    
    TRUNCATE TABLE
   
   
   
   

    
    digoal=> insert into tbl_time select generate_series(1,10000),clock_timestamp(),now();
   
   
   
   

    
    INSERT 0 10000
   
   
   
   

    
    digoal=> select count(*),count(distinct row_time),count(distinct stat_time) from tbl_time;
   
   
   
   

    
     count | count | count 
   
   
   
   

    
    -------+-------+-------
   
   
   
   

    
     10000 | 10000 |     1
   
   
   
   

    
    (1 row)
   
   

# 看来插入的时候还是每一个ROW运行一次。

所以三个状态都是定义层面的，不是完全的执行层面的。