-
理解函数的三态1
VOLATILE：
volatile函数没有限制,可以修改数据(如执行delete,insert,update), 使用同样的参数调用可能返回不同的值.STABLE：
不允许修改数据, PG8.0以及以上版本不允许在volatile函数中使用非SELECT|PERFORM语句. 使用同样的参数调用返回同样的结果,在事务中有这个特性的也归属stable.IMMUTABLE：
不允许修改数据, 使用同样的参数调用返回同样的结果.备注：Pg 8.0以及以上版本不允许在stable或immutable函数中执行非select|perform语句.
示例：
test=# create table tbl(id int primary key, info text, crt_time timestamp); CREATE TABLE test=# create or replace function f_tbl(i_id int) returns void as $$ test$# declare test$# begin test$# update tbl set crt_time=now() where id=i_id; test$# end; test$# $$ language plpgsql stable; CREATE FUNCTION test=# \set VERBOSITY verbose test=# select f_tbl(1); ERROR: 0A000: UPDATE is not allowed in a non-volatile function 背景: SQL statement "update tbl set crt_time=now() where id=i_id" PL/pgSQL function f_tbl(integer) line 4 at SQL statement 位置: _SPI_execute_plan_internal, spi.c:2951漏洞:在stable或immutable函数中调用volatile函数是可以的.
示例：
test=# alter function f_tbl(int) volatile; ALTER FUNCTION test=# create or replace function f_tbl1(i_id int) returns void as $$ test$# declare test$# begin test$# perform f_tbl(i_id); -- 在stable或immutable函数中调用volatile函数是可以的. test$# end; test$# $$ language plpgsql stable; CREATE FUNCTION test=# test=# insert into tbl values(1,'test',now()); INSERT 0 1 test=# select * from tbl; id | info | crt_time ----+------+---------------------------- 1 | test | 2022-11-10 11:46:05.854350 (1 行记录) test=# select f_tbl1(1); f_tbl1 -------- (1 行记录) test=# select * from tbl; id | info | crt_time ----+------+---------------------------- 1 | test | 2022-11-10 11:46:33.153699 (1 行记录)同样的参数多次调用.
volatile 函数相同的参数,多次调用返回结果可能不一样.
kbstattuple扩展插件可以参考KingbaseES在线手册 https://help.kingbase.com.cn/v8/development/sql-plsql/ref-extended-plug-in/kbstattuple.html?highlight=kbstattuple test=# create extension kbstattuple ; CREATE EXTENSION test=# \dx+ kbstattuple ; 对象用于扩展 "kbstattuple" 对象描述 --------------------------------------- function pg_relpages(regclass) function pg_relpages(text) function pgstatginindex(regclass) function pgstathashindex(regclass) function pgstatindex(regclass) function pgstatindex(text) function pgstattuple_approx(regclass) function pgstattuple(regclass) function pgstattuple(text) (9 行记录) test=# create table t2(id int); CREATE TABLE test=# select pg_relpages('t2'); pg_relpages ------------- 0 (1 行记录) test=# insert into t2 values (1); INSERT 0 1 test=# select pg_relpages('t2'); -- 返回值变化 pg_relpages ------------- 1 (1 行记录) test=# select proname,provolatile from pg_proc where proname='pg_relpages'; proname | provolatile -------------+------------- pg_relpages | v pg_relpages | v (2 行记录)stable, immutable函数同样的参数多次调用返回结果不变.
在事务中多次调用返回结果一致的也可归属于stable.
test=# select now(); now ------------------------------- 2022-11-10 13:42:35.689740+08 (1 行记录) test=# select now(); now ------------------------------- 2022-11-10 13:42:36.756038+08 (1 行记录) test=# begin; BEGIN test=# select now(); now ------------------------------- 2022-11-10 13:42:39.037893+08 (1 行记录) test=# select now(); now ------------------------------- 2022-11-10 13:42:39.037893+08 (1 行记录) test=# select provolatile,proname,proargtypes from pg_proc where proname='now'; provolatile | proname | proargtypes -------------+---------+------------- s | now | (1 行记录) test=# select now(); now ------------------------------- 2022-11-10 13:42:39.037893+08 (1 行记录) test=# end; COMMIT test=# select now(); now ------------------------------- 2022-11-10 13:43:31.772061+08 (1 行记录)immutable函数同stable, 同样的参数多次调用结果一致.
test=# select proname,provolatile from pg_proc where proname='abs'; proname | provolatile ---------+------------- abs | i abs | i abs | i abs | i abs | i abs | i abs | i (7 行记录) test=# select abs(-10); abs ----- 10 (1 行记录) test=# select abs(-10); abs ----- 10 (1 行记录) test=# begin; BEGIN test=# select abs(-10); abs ----- 10 (1 行记录) test=# select abs(-10); abs ----- 10 (1 行记录) test=# end; COMMIT test=# select abs(-10); abs ----- 10 (1 行记录) -
VOLATILE/STABLE/IMMUTABLE区别：
VOLATILE
volatile函数不能被优化器作为优化条件.
1）例如单SQL处理多行时不能减少volatile函数的调用次数,
2）不能使用volatile函数创建函数索引,
3）在过滤条件中使用volatile函数时, 不能走索引扫描.
在同一个查询中, 同样参数的情况下可能被多次执行(QUERY有多行返回/扫描的情况下).
STABLE
1）优化器可根据实际场景优化stable函数的调用次数, 同样的参数多次调用可能减少成单次调用.
2）stable函数可以用于优化器选择合适的索引扫描, 因为索引扫描仅评估被比较的值一次, 后多次比较.
3）stable和volatile函数都不能用于创建函数索引, 只有immutable函数可以用于创建函数索引.
IMMUTABLE
优化器在处理immutable函数时, 先评估函数结果, 将结果替换为常量.
-
理解函数的三态2
test=# create table t3(id int); CREATE TABLE test=# insert into t3 values(1),(1),(1),(2),(2),(2); INSERT 0 6 test=# test=# create or replace function f_t3(id int) returns int as $$ test$# declare test$# begin test$# raise notice 'Called.'; test$# return id; test$# end; test$# $$ language plpgsql stable; -- stable 状态 CREATE FUNCTION test=# select * from t3 where f_t3(id) =1; -- f_t3(id) --这里id是变量,不能被优化器优化,因此函数调用了6次. test=# select * from t3 where f_t3(id) =1; NOTICE: 00000: Called. 位置: exec_stmt_raise, pl_exec.c:3908 NOTICE: 00000: Called. 位置: exec_stmt_raise, pl_exec.c:3908 NOTICE: 00000: Called. 位置: exec_stmt_raise, pl_exec.c:3908 NOTICE: 00000: Called. 位置: exec_stmt_raise, pl_exec.c:3908 NOTICE: 00000: Called. 位置: exec_stmt_raise, pl_exec.c:3908 NOTICE: 00000: Called. 位置: exec_stmt_raise, pl_exec.c:3908 id ---- 1 1 1 (3 行记录) select * from t3 where f_t3(1) =1; -- 这里使用常量调用f_t3()所以可以被优化器优化. test=# select * from t3 where f_t3(1) =1; NOTICE: 00000: Called. -- 函数只被调用一次 位置: exec_stmt_raise, pl_exec.c:3908 id ---- 1 1 1 2 2 2 (6 行记录) 把函数改成volatile后, 函数不能被优化. test=# alter function f_t3(int) volatile; ALTER FUNCTION test=# select proname,provolatile from pg_proc where proname='f_t3'; proname | provolatile ---------+------------- f_t3 | v (1 行记录) test=# select * from t3 where f_t3(1) =1; NOTICE: 00000: Called. 位置: exec_stmt_raise, pl_exec.c:3908 NOTICE: 00000: Called. 位置: exec_stmt_raise, pl_exec.c:3908 NOTICE: 00000: Called. 位置: exec_stmt_raise, pl_exec.c:3908 NOTICE: 00000: Called. 位置: exec_stmt_raise, pl_exec.c:3908 NOTICE: 00000: Called. 位置: exec_stmt_raise, pl_exec.c:3908 NOTICE: 00000: Called. 位置: exec_stmt_raise, pl_exec.c:3908 id ---- 1 1 1 2 2 2 (6 行记录)
根据函数的实际情况设置稳定态, 可以达到优化效果.
例如f_t3函数调用一次耗时1秒, 并且是stable的状态,以上例子可以减少5秒的查询时间.使用volatile态则需要6秒.
优化器在处理immutable函数时,先评估函数结果,将结果替换为常量.
test=# explain select * from t2 where id>abs(-1);
QUERY PLAN
-----------------------------------------------------
Seq Scan on t2 (cost=0.00..41.75 rows=847 width=4)
Filter: (id > 1)
(2 行记录)
因为abs(int)是immutable函数,这里abs(-1)替换成常量1.
如果把函数改成stable, 那么将不会替换成常量.
test=# alter function abs(int) stable;
ALTER FUNCTION
test=# select proname,provolatile from pg_proc where proname='abs';
proname | provolatile
---------+-------------
abs | i
abs | i
abs | i
abs | i
abs | i
abs | s
abs | i
(7 行记录)
test=# explain select * from t2 where id>abs(-1);
QUERY PLAN
-----------------------------------------------------
Seq Scan on t2 (cost=0.00..48.10 rows=847 width=4)
Filter: (id > abs('-1'::integer))
(2 行记录)
由于abs(int)被改成stable了, 将不会替换成常量
在prepared statement中使用需要注意区别. 后面会有例子.
只有immutable函数可以创建函数索引.
test=# create table t4(id int,info timestamp(0));
CREATE TABLE
test=# \set VERBOSITY verbose
test=# create index idx_t4_1 on t4(to_char(info,'yyyymmdd'));
ERROR: 42P17: functions in index expression must be marked IMMUTABLE
位置: ComputeIndexAttrs, indexcmds.c:1940
test=# create table t5(id int primary key, info text);
CREATE TABLE
test=# insert into t5 select generate_series(1,100000),md5(random()::text);
INSERT 0 100000
test=# alter function abs(int) volatile;
ALTER FUNCTION
test=#
test=# \d+ t4
数据表 "public.t4"
栏位 | 类型 | 校对规则 | 可空的 | 预设 | 存储 | 统计目标 | 描述
------+--------------------------------+----------+--------+------+-------+----------+------
id | integer | | | | plain | |
info | timestamp(0) without time zone | | | | plain | |
访问方法 heap
test=# \d+ t5
数据表 "public.t5"
栏位 | 类型 | 校对规则 | 可空的 | 预设 | 存储 | 统计目标 | 描述
------+---------+----------+----------+------+----------+----------+------
id | integer | | not null | | plain | |
info | text | | | | extended | |
索引：
"t5_pkey" PRIMARY KEY, btree (id)
访问方法 heap
索引扫描时, 用于过滤条件的表达式只被评估一次后, 再与索引值进行比较判断是否满足条件.
test=# explain select * from t5 where id<abs(10);
QUERY PLAN
----------------------------------------------------------
Seq Scan on t5 (cost=0.00..2341.00 rows=33333 width=37)
Filter: (id < abs(10))
(2 行记录)
只有stable函数和immutable函数符合索引扫描的刚性需求.
test=# alter function abs(int) stable;
ALTER FUNCTION
test=# explain select * from t5 where id<abs(-100);
QUERY PLAN
--------------------------------------------------------------------
Index Scan using t5_pkey on t5 (cost=0.29..9.99 rows=97 width=37)
Index Cond: (id < abs('-100'::integer))
(2 行记录)
test=# alter function abs(int) immutable;
ALTER FUNCTION
test=# explain select * from t5 where id<abs(-100);
QUERY PLAN
--------------------------------------------------------------------
Index Scan using t5_pkey on t5 (cost=0.29..9.99 rows=97 width=37)
Index Cond: (id < 100)
(2 行记录)
volatile函数同样的输入参数可能返回不同值,在一个查询中将被多次调用,不符合索引扫描规则.
stable和immutable同样的参数返回值不变,因此可以作为索引扫描的比较值,优化器允许走索引扫描.
-
理解函数的三态3
函数内的每条查询语句的数据可见性:
VOLATILE
snapshot为函数内的每个query开始时的snapshot. 因此对外部已提交的数据时可见的.STABLE
snapshot为外部调用函数的QUERY的snapshot, 函数内部始终保持这个snapshot.IMMUTABLE
同stable
测试：
test=# create or replace function f_t6() returns void as $$
test$# declare
test$# r record;
test$# begin
test$# for i in 1..10 loop
test$# for r in select * from t6 loop
test$# raise notice 'loop:%,t6:%.',i,r;
test$# end loop;
test$# perform pg_sleep(5);
test$# end loop;
test$# end;
test$# $$ language plpgsql volatile;
CREATE FUNCTION
test=# create table t6(id int,info text);
CREATE TABLE
--执行过程中,新建连接往t6表插入新数据,volatile函数察觉新增行
test=# select f_t6();
NOTICE: loop:2,t6:(1,test).
NOTICE: loop:3,t6:(1,test).
NOTICE: loop:4,t6:(1,test).
NOTICE: loop:5,t6:(1,test).
NOTICE: loop:5,t6:(1,test1).
NOTICE: loop:6,t6:(1,test).
NOTICE: loop:6,t6:(1,test1).
NOTICE: loop:6,t6:(1,test3).
NOTICE: loop:7,t6:(1,test).
NOTICE: loop:7,t6:(1,test1).
NOTICE: loop:7,t6:(1,test3).
NOTICE: loop:7,t6:(1,test5).
NOTICE: loop:8,t6:(1,test).
NOTICE: loop:8,t6:(1,test1).
NOTICE: loop:8,t6:(1,test3).
NOTICE: loop:8,t6:(1,test5).
NOTICE: loop:9,t6:(1,test).
NOTICE: loop:9,t6:(1,test1).
NOTICE: loop:9,t6:(1,test3).
NOTICE: loop:9,t6:(1,test5).
NOTICE: loop:10,t6:(1,test).
NOTICE: loop:10,t6:(1,test1).
NOTICE: loop:10,t6:(1,test3).
NOTICE: loop:10,t6:(1,test5).
NOTICE: loop:10,t6:(1,hello).
f_t6
------
(1 行记录)
test=# insert into t6 values(1,'test');
INSERT 0 1
test=# insert into t6 values(1,'test1');
INSERT 0 1
test=# insert into t6 values(1,'test3');
INSERT 0 1
test=# insert into t6 values(1,'test5');
INSERT 0 1
test=# insert into t6 values(1,'hello');
INSERT 0 1
test=#
--更改稳定态为 stable immutable
test=# alter function f_t6() stable;
ALTER FUNCTION
test=# truncate table t6;
TRUNCATE TABLE
test=# select f_t6();
f_t6
------
(1 行记录)
--f_t6()执行过程中对t6变更, 函数不感知.
test=# insert into t6 values(1,'hello');
INSERT 0 1
test=# insert into t6 values(1,'test5');
INSERT 0 1
test=# insert into t6 values(1,'test3');
INSERT 0 1
test=# insert into t6 values(1,'test3');
INSERT 0 1
test=#
-
理解函数的三态4
STABLE和IMMUTABLE的区别:
在SELECT子句中,优化器对stable和immutable区别对待.测试：
test=# create table t7(id int); CREATE TABLE test=# insert into t7 values (1),(2),(3); INSERT 0 3 test=# create or replace function f_t7(i int) returns int as $$ test$# declare test$# begin test$# raise notice 'called'; test$# return i; test$# end; test$# $$ language plpgsql stable; CREATE FUNCTION -- SELECT子句中, stable函数不被优化器优化 test=# select f_t7(1),* from t7; NOTICE: called --多次调用 NOTICE: called NOTICE: called f_t7 | id ------+---- 1 | 1 1 | 2 1 | 3 (3 行记录) test=# alter function f_t7(int) immutable; ALTER FUNCTION -- immutable函数被替换成常量, 只执行一次. test=# select f_t7(1),* from t7; NOTICE: called --一次调用 f_t7 | id ------+---- 1 | 1 1 | 2 1 | 3 (3 行记录) --当函数为stable时, 优化器没有处理这种过滤条件, 理论上是可以优化为一次调用f_t7(1)的. 目前仅仅immutable被优化. test=# alter function f_t7(int) stable; ALTER FUNCTION 当使用索引扫描时, stable在这里只会执行一次. test=# select * from t7 where id=f_t7(1); NOTICE: called --有一次为explain的评估输出. NOTICE: called NOTICE: called NOTICE: called id ---- 1 (1 行记录) -- 将id=f_t7(1)替换成f_t7(1)=1 test=# select * from t7 where f_t7(1)=1; NOTICE: called id ---- 1 2 3 (3 行记录) test=# explain select * from t7 where f_t7(1)=1; QUERY PLAN ------------------------------------------------------------ Result (cost=0.25..35.65 rows=2540 width=4) One-Time Filter: (f_t7(1) = 1) -> Seq Scan on t7 (cost=0.25..35.65 rows=2540 width=4) (3 行记录) test=# alter function f_t7(int) immutable; ALTER FUNCTION test=# explain select * from t7 where f_t7(1)=1; NOTICE: called QUERY PLAN ------------------------------------------------------ Seq Scan on t7 (cost=0.00..35.40 rows=2540 width=4) (1 行记录) -
理解函数的三态
VOLATILE
1)volatile函数没有限制, 可以修改数据(如执行delete, insert , update). 2)使用同样的参数调用可能返回不同的值. 3)volatile函数不能被优化器选择作为优化条件.(例如减少调用, 函数索引, 索引扫描不允许使用volatile函数) 4)在同一个查询中, 同样参数的情况下可能被多次执行(QUERY有多行返回/扫描的情况下). 5)snapshot为函数内的每个query开始时的snapshot. 因此对在函数执行过程中, 外部已提交的数据可见.(仅限于调用函数的事务隔离级别为read committed)
STABLE
1)stable和immutable函数, 函数内不允许修改数据.(如PGver>=8.0 函数内不可执行非SELECT|PERFORM语句.)
2)使用同样的参数调用返回同样的结果, 在事务中有这个特性的也归属stable.
3)优化器可根据实际场景优化stable函数的调用次数, 同样的参数多次调用可减少成单次调用.
4)stable和immutable函数可用于优化器选择合适的索引扫描, 因为索引扫描仅评估被比较的表达式一次, 后多次与索引值进行比较.
5)stable和volatile函数都不能用于创建函数索引, 只有immutable函数可以用于创建函数索引.
6)stable和immutable函数, snapshot为外部调用函数的QUERY的snapshot, 函数内部始终保持这个snapshot, 外部会话带来的的数据变更不被反映到函数执行过程中.
IMMUTABLE
1)不允许修改数据, 使用同样的参数调用返回同样的结果.
2)优化器在处理immutable函数时, 先评估函数结果, 将结果替换为常量.
3)因此使用约束优化查询的场景中也只识别immutable函数.
STABLE和IMMUTABLE的区别
1)STABLE函数在SELECT和WHERE子句中不被优化, 仅仅当使用索引扫描时WHERE子句对应的STABLE函数才会被优化为1次调用.
2)在PREPARED STATEMENT中的使用区别:
3)IMMUTABLE函数在PLAN时以常量替代,STABLE函数在EXECUTE阶段被执行.
4)因此IMMUTABLE函数参数为常量时,在PREPARED STATEMENT场景只执行一次,而STABLE函数被多次执行.
函数稳定性通过查看PG_PROC/SYS_PROC.PROVOLATILE得到
PROVOLATILE 说明函数是仅仅只依赖 于它的输入参数 ，还是会被外部 因素影响。
值 I 表示“不变的”函数,它对于相同的输入总是输出相同的结果。
值 S 表示“稳定的”函数,它的结果(对于固定输入在一次扫描内不会变化。
值 V 表示“不稳定的”函数,它的结果在任何时候都可能变化。
使用V页表示函数具有副作用,所以对它们的调用无法得到优化。