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 stable
 timeofday                              | v           |        0 volatile
 now                                    | s           |        0
 transaction_timestamp                  | s           |        0
 statement_timestamp                    | s           |        0
 clock_timestamp                        | v           |        0

其中
clock_timestamp是voatile的.now是stable的。

   
halee => 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
halee=> select count(*),count(distinct row_time),count(distinct stat_time) from tbl_time;
 count | count | count 
-------+-------+-------
 10000 | 10000 |     1
(1 row)

# 情况已经很明朗了
volatile每一行都运算了,stable的只是STATEMANT开始时运算。
# 再来看看索引的比较
   
halee=> 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)
halee=> 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试试。马上就走索引了。不过这个不能乱改.
   
halee=> \c halee postgres
You are now connected to database "digoal" as user "postgres".
halee=# alter function clock_timestamp() strict stable;
ALTER FUNCTION
halee=# \c digoal digoal
You are now connected to database "digoal" as user "digoal".
halee=> 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)

# 那么看看插入会不会受到影响
    
halee=> truncate table tbl_time;
TRUNCATE TABLE
halee=> insert into tbl_time select generate_series(1,10000),clock_timestamp(),now();
INSERT 0 10000
halee=> select count(*),count(distinct row_time),count(distinct stat_time) from tbl_time;
 count | count | count 
-------+-------+-------
 10000 | 10000 |     1
(1 row)
# 看来插入的时候还是每一个ROW运行一次。

所以三个状态都是定义层面的,不是完全的执行层面的。
相关推荐
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页