plpgsql_check插件,用于解决plpgsql相关的问题,比如,对象是否定义,检测类型的一致性,类型定义错误,SQL注入,性能分析,未使用变量或者参数等等
PostgreSQL 9.5以上均支持改插件
git地址:
https://github.com/okbob/plpgsql_check#tracer
安装:
unzip /home/postgres/plpgsql_check-master.zip
chown -R postgres.postgres plpgsql_check-master
cd plpgsql_check-master
make USE_PGXS=1 clean
make USE_PGXS=1 install
1
2
3
4
5
主动模式:
以下例子说明了,f1函数调用了t1的c列,但是该表没有c列,主动使用plpgsql_check_function_tb可以检测出该错误
postgres=# CREATE EXTENSION plpgsql_check;
LOAD
postgres=# CREATE TABLE t1(a int, b int);
CREATE TABLE
postgres=#
CREATE OR REPLACE FUNCTION public.f1()
RETURNS void
LANGUAGE plpgsql
AS
f
u
n
c
t
i
o
n
function
function
DECLARE r record;
BEGIN
FOR r IN SELECT * FROM t1
LOOP
RAISE NOTICE ‘%’, r.c; – there is bug - table t1 missing “c” column
END LOOP;
END;
f
u
n
c
t
i
o
n
function
function;
CREATE FUNCTION
postgres=# select f1(); – execution doesn’t find a bug due to empty table t1
f1
────
(1 row)
postgres=# \x
Expanded display is on.
postgres=# select * from plpgsql_check_function_tb(‘f1()’);
─[ RECORD 1 ]───────────────────────────
functionid │ f1
lineno │ 6
statement │ RAISE
sqlstate │ 42703
message │ record “r” has no field “c”
detail │ [null]
hint │ [null]
level │ error
position │ 0
query │ [null]
postgres=# \sf+ f1
CREATE OR REPLACE FUNCTION public.f1()
RETURNS void
LANGUAGE plpgsql
AS
f
u
n
c
t
i
o
n
function
function
DECLARE r record;
BEGIN
FOR r IN SELECT * FROM t1
LOOP
RAISE NOTICE ‘%’, r.c; – there is bug - table t1 missing “c” column
END LOOP;
END;
f
u
n
c
t
i
o
n
function
function
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
可以使用以下语句大批量检测相关函数
– check all nontrigger plpgsql functions
SELECT p.oid, p.proname, plpgsql_check_function(p.oid)
FROM pg_catalog.pg_namespace n
JOIN pg_catalog.pg_proc p ON pronamespace = n.oid
JOIN pg_catalog.pg_language l ON p.prolang = l.oid
WHERE l.lanname = ‘plpgsql’ AND p.prorettype <> 2279;
或者
SELECT p.proname, tgrelid::regclass, cf.*
FROM pg_proc p
JOIN pg_trigger t ON t.tgfoid = p.oid
JOIN pg_language l ON p.prolang = l.oid
JOIN pg_namespace n ON p.pronamespace = n.oid,
LATERAL plpgsql_check_function(p.oid, t.tgrelid) cf
WHERE n.nspname = ‘public’ and l.lanname = ‘plpgsql’
或者
– check all plpgsql functions (functions or trigger functions with defined triggers)
SELECT
(pcf).functionid::regprocedure, (pcf).lineno, (pcf).statement,
(pcf).sqlstate, (pcf).message, (pcf).detail, (pcf).hint, (pcf).level,
(pcf).“position”, (pcf).query, (pcf).context
FROM
(
SELECT
plpgsql_check_function_tb(pg_proc.oid, COALESCE(pg_trigger.tgrelid, 0)) AS pcf
FROM pg_proc
LEFT JOIN pg_trigger
ON (pg_trigger.tgfoid = pg_proc.oid)
WHERE
prolang = (SELECT lang.oid FROM pg_language lang WHERE lang.lanname = ‘plpgsql’) AND
pronamespace <> (SELECT nsp.oid FROM pg_namespace nsp WHERE nsp.nspname = ‘pg_catalog’) AND
– ignore unused triggers
(pg_proc.prorettype <> (SELECT typ.oid FROM pg_type typ WHERE typ.typname = ‘trigger’) OR
pg_trigger.tgfoid IS NOT NULL)
OFFSET 0
) ss
ORDER BY (pcf).functionid::regprocedure::text, (pcf).lineno
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
被动模式
在启动时就有检查功能-必须加载plpgsql_check模块。
可以在postgresql.conf中加入该参数
plpgsql_check.mode = [ disabled | by_function | fresh_start | every_start ]
plpgsql_check.fatal_errors = [ yes | no ]
plpgsql_check.show_nonperformance_warnings = false
plpgsql_check.show_performance_warnings = false
1
2
3
4
5
还需要shared_preload_libraries加入我们用的插件,如下:
hank=# show shared_preload_libraries ;
shared_preload_libraries
pg_pathman,plpgsql,plpgsql_check
1
2
3
4
如:
#plpgsql_check
plpgsql_check.mode = every_start
plpgsql_check.fatal_errors = on
plpgsql_check.show_nonperformance_warnings = true
plpgsql_check.show_performance_warnings = true
1
2
3
4
5
通过以下方式启动被动模式:
load ‘plpgsql’; – 1.1 and higher doesn’t need it
load ‘plpgsql_check’;
set plpgsql_check.mode = ‘every_start’;
SELECT fx(10); – run functions - function is checked before runtime starts it
1
2
3
4
5
plpgsql_check无法验证对在plpgsql函数运行时中创建的临时表的查询。以下例子,有必要创建一个伪造的临时表或为此功能禁用plpgsql_check。实际中,临时表以比持久表有更高的优先级存储在自己的(每用户)模式中。因此,您可以这样做
CREATE OR REPLACE FUNCTION public.disable_dml()
RETURNS trigger
LANGUAGE plpgsql AS
f
u
n
c
t
i
o
n
function
function
BEGIN
RAISE EXCEPTION SQLSTATE ‘42P01’
USING message = format(‘this instance of %I table doesn’‘t allow any DML operation’, TG_TABLE_NAME),
hint = format(‘you should to run “CREATE TEMP TABLE %1
I
(
L
I
K
E
I(LIKE %1
I(LIKEI INCLUDING ALL);” statement’,
TG_TABLE_NAME);
RETURN NULL;
END;
f
u
n
c
t
i
o
n
function
function;
CREATE TABLE foo(a int, b int); – doesn’t hold data ever
CREATE TRIGGER foo_disable_dml
BEFORE INSERT OR UPDATE OR DELETE ON foo
EXECUTE PROCEDURE disable_dml();
postgres=# INSERT INTO foo VALUES(10,20);
ERROR: this instance of foo table doesn’t allow any DML operation
HINT: you should to run “CREATE TEMP TABLE foo(LIKE foo INCLUDING ALL);” statement
postgres=#
CREATE TABLE
postgres=# INSERT INTO foo VALUES(10,20);
INSERT 0 1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
hank=# CREATE OR REPLACE FUNCTION public.fx1(a integer)
hank-# RETURNS integer
hank-# LANGUAGE plpgsql
hank-# AS
f
u
n
c
t
i
o
n
function
function
hankKaTeX parse error: Expected 'EOF', got '#' at position 1: #̲ begin hank# if a > 10 then
hankKaTeX parse error: Expected 'EOF', got '#' at position 1: #̲ raise not…# return -1;
hankKaTeX parse error: Expected 'EOF', got '#' at position 1: #̲ else hank# raise notice ‘nazdar’;
hankKaTeX parse error: Expected 'EOF', got '#' at position 1: #̲ return 1;…# end if;
hankKaTeX parse error: Expected 'EOF', got '#' at position 1: #̲ end; hank#
f
u
n
c
t
i
o
n
function
function;
CREATE FUNCTION
hank=#
hank=#
每个语句(不是每行)的概要可以通过plpgsql_profiler_function_statements_tb查看
hank=# select stmtid, parent_stmtid, parent_note, lineno, exec_stmts, stmtname
hank-# from plpgsql_profiler_function_statements_tb(‘fx1’);
stmtid | parent_stmtid | parent_note | lineno | exec_stmts | stmtname
--------±--------------±------------±-------±-----------±----------------
0 | | | 2 | 0 | statement block
1 | 0 | body | 3 | 0 | IF
2 | 1 | then body | 4 | 0 | RAISE
3 | 1 | then body | 5 | 0 | RETURN
4 | 1 | else body | 7 | 0 | RAISE
5 | 1 | else body | 8 | 0 | RETURN
(6 rows)
可以通过plpgsql_profiler_function_tb查看fx1的分析结果
hank=# select lineno, avg_time, source from plpgsql_profiler_function_tb(‘fx1(int)’);
lineno | avg_time | source
--------±---------±----------------------------
1 | |
2 | | begin
3 | | if a > 10 then
4 | | raise notice ‘ahoj’;
5 | | return -1;
6 | | else
7 | | raise notice ‘nazdar’;
8 | | return 1;
9 | | end if;
10 | | end;
11 | |
(11 rows)
其他有关trace,sql安装,以及安全相关的示例这里不再演示,请参考以下链接:
https://github.com/digoal/blog/blob/master/201908/20190831_01.md
https://github.com/okbob/plpgsql_check#tracer