PostgreSQL DBA(72) - Extension(pgplsql_check)

PostgreSQL在创建存储过程/函数时没有执行语义分析/校验,可以用plpgsql_check这个插件进行检查,除此之外,该插件还能发现函数依赖和进行函数性能分析.
本节简单介绍该extension的安装和使用。

pplpgsql_check可完成下面3个工作:
1.Checking for compilation errors in a function code
2.Finding dependencies in functions
3.Profiling functions

Features包括:

1.check fields of referenced database objects and types inside embedded SQL
2.using correct types of function parameters
3.unused variables and function argumens, unmodified OUT argumens
4.partially detection of dead code (due RETURN command)
5.detection of missing RETURN command in function
6.try to identify unwanted hidden casts, that can be performance issue like unused indexes
7.possibility to collect relations and functions used by function
8.possibility to check EXECUTE stmt agaist SQL injection vulnerability

安装
从github上下载源码,make/make install

[pg12@localhost plpgsql_check]$ pwd/data/source/postgresql-12beta1/contrib/plpgsql_check[pg12@localhost plpgsql_check]$ ls_config.yml             plpgsql_check.control            postgresql95-plpgsql_check.specexpected                plpgsql_check.so                 postgresql96-plpgsql_check.specLICENSE                 postgresql10-plpgsql_check.spec  README.mdMakefile                postgresql11-plpgsql_check.spec  sqlMETA.json               postgresql12-plpgsql_check.spec  srcmsvc                    postgresql13-plpgsql_check.spec  TODO.mdplpgsql_check--1.7.sql  postgresql94-plpgsql_check.spec[pg12@localhost plpgsql_check]$ make...[pg12@localhost plpgsql_check]$ sudo make installmake -C ../../src/backend generated-headersmake[1]: Entering directory `/data/source/postgresql-12beta1/src/backend'make -C catalog distprep generated-header-symlinksmake[2]: Entering directory `/data/source/postgresql-12beta1/src/backend/catalog'make[2]: Nothing to be done for `distprep'.make[2]: Nothing to be done for `generated-header-symlinks'.make[2]: Leaving directory `/data/source/postgresql-12beta1/src/backend/catalog'make -C utils distprep generated-header-symlinksmake[2]: Entering directory `/data/source/postgresql-12beta1/src/backend/utils'make[2]: Nothing to be done for `distprep'.make[2]: Nothing to be done for `generated-header-symlinks'.make[2]: Leaving directory `/data/source/postgresql-12beta1/src/backend/utils'make[1]: Leaving directory `/data/source/postgresql-12beta1/src/backend'/usr/bin/mkdir -p '/appdb/xdb/pg12beta1/lib/postgresql'/usr/bin/mkdir -p '/appdb/xdb/pg12beta1/share/postgresql/extension'/usr/bin/mkdir -p '/appdb/xdb/pg12beta1/share/postgresql/extension'/usr/bin/install -c -m 755  plpgsql_check.so '/appdb/xdb/pg12beta1/lib/postgresql/plpgsql_check.so'/usr/bin/install -c -m 644 ./plpgsql_check.control '/appdb/xdb/pg12beta1/share/postgresql/extension/'/usr/bin/install -c -m 644 ./plpgsql_check--1.7.sql  '/appdb/xdb/pg12beta1/share/postgresql/extension/'

plpgsql_check插件需要预加载动态链接库,需修改postgresql.conf文件

[pg12@localhost pg12db1]$ grep 'shared_preload' postgresql.confshared_preload_libraries = 'pg_stat_statements,pg_qualstats,plpgsql,plpgsql_check'  # (change requires restart)[pg12@localhost pg12db1]$ pg_ctl restartwaiting for server to shut down.... doneserver stoppedwaiting for server to start....2019-08-09 12:07:00.242 CST [2086] LOG:  starting PostgreSQL 12beta1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit2019-08-09 12:07:00.243 CST [2086] LOG:  listening on IPv4 address "0.0.0.0", port 54322019-08-09 12:07:00.243 CST [2086] LOG:  listening on IPv6 address "::", port 54322019-08-09 12:07:00.256 CST [2086] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"2019-08-09 12:07:00.407 CST [2086] LOG:  redirecting log output to logging collector process2019-08-09 12:07:00.407 CST [2086] HINT:  Future log output will appear in directory "pg_log". doneserver started

创建extension

[local]:5432 pg12@testdb=# create extension plpgsql_check;CREATE EXTENSIONTime: 235.761 ms

语义检查
数据表t_noexists不存在,但PG并没有执行语义检查

[local]:5432 pg12@testdb=# CREATE OR REPLACE PROCEDURE sp_error(int) pg12@testdb-# ASpg12@testdb-# $$pg12@testdb$# declarepg12@testdb$#  v_id integer := 0;pg12@testdb$# begin pg12@testdb$#     SELECT 1 FROM t_notexists;pg12@testdb$# end;pg12@testdb$# $$  LANGUAGE plpgsql;CREATE PROCEDURETime: 2.265 ms[local]:5432 pg12@testdb=# call sp_error(1);ERROR:  relation "t_notexists" does not existLINE 1: SELECT 1 FROM t_notexists                      ^QUERY:  SELECT 1 FROM t_notexistsCONTEXT:  PL/pgSQL function sp_error(integer) line 5 at SQL statementTime: 2.743 ms[local]:5432 pg12@testdb=#

通过plpgsql_check_function_tb检查语义错误

[local]:5432 pg12@testdb=# select * from plpgsql_check_function_tb('sp_error(int)');-[ RECORD 1 ]-------------------------------------functionid | sp_errorlineno     | 5statement  | SQL statementsqlstate   | 42P01message    | relation "t_notexists" does not existdetail     | hint       | level      | errorposition   | 15query      | SELECT 1 FROM t_notexistscontext    | Time: 19.023 ms

但对于未声明的变量,没有校验(下例中的x)或者错误的认为是列名(v_id1)

[local]:5432 pg12@testdb=# CREATE OR REPLACE PROCEDURE sp_error2(int) pg12@testdb-# ASpg12@testdb-# $$pg12@testdb$# declarepg12@testdb$#  v_id integer := 0;pg12@testdb$# begin pg12@testdb$# raise notice 'id is %',v_id1;pg12@testdb$# raise notice 'id is %',x;pg12@testdb$# end;pg12@testdb$# $$  LANGUAGE plpgsql;CREATE PROCEDURETime: 1.152 ms[local]:5432 pg12@testdb=# select * from plpgsql_check_function_tb('sp_error2(int)');-[ RECORD 1 ]-----------------------------functionid | sp_error2lineno     | 5statement  | RAISEsqlstate   | 42703message    | column "v_id1" does not existdetail     | hint       | level      | errorposition   | 8query      | SELECT v_id1context    | Time: 3.950 ms[local]:5432 pg12@testdb=# select * from plpgsql_check_function('sp_error2(int)');              plpgsql_check_function               --------------------------------------------------- error:42703:5:RAISE:column "v_id1" does not exist Query: SELECT v_id1 --            ^(3 rows)Time: 4.669 ms

函数依赖
通过plpgsql_show_dependency_tb函数,可查询对象(存储过程/函数等)依赖

[local]:5432 pg12@testdb=# CREATE OR REPLACE FUNCTION sp_func1(int) pg12@testdb-# RETURNS INTpg12@testdb-# ASpg12@testdb-# $$pg12@testdb$# declarepg12@testdb$#  v_id integer := 0;pg12@testdb$# begin pg12@testdb$# raise notice 'id is %',v_id1;pg12@testdb$# raise notice 'id is %',x;pg12@testdb$# return 0;pg12@testdb$# end;pg12@testdb$# $$  LANGUAGE plpgsql; integer := 0;begin     raise notice 'id is %',v_id1;    raise notice 'id is %',x;    select sp_func1(v_id) into v_id;end;$$  LANGUAGE plpgsql;select  plpgsql_show_dependency_tb('sp_error3(int)');CREATE FUNCTIONTime: 4.135 ms[local]:5432 pg12@testdb=# [local]:5432 pg12@testdb=# CREATE OR REPLACE PROCEDURE sp_error3(int) pg12@testdb-# ASpg12@testdb-# $$pg12@testdb$# declarepg12@testdb$#  v_id integer := 0;pg12@testdb$# begin pg12@testdb$# raise notice 'id is %',v_id1;pg12@testdb$# raise notice 'id is %',x;pg12@testdb$# select sp_func1(v_id) into v_id;pg12@testdb$# end;pg12@testdb$# $$  LANGUAGE plpgsql;CREATE PROCEDURETime: 2.856 ms[local]:5432 pg12@testdb=# [local]:5432 pg12@testdb=# select lineno, avg_time, source from plpgsql_show_dependency_tb('sp_error3(int)');          plpgsql_show_dependency_tb           ----------------------------------------------- (FUNCTION,303253,public,sp_func1,"(integer)")(1 row)Time: 3.489 ms

性能分析
开启性能分析选项,执行过程,查询性能数据

[local]:5432 pg12@testdb=# SET plpgsql_check.profiler TO 'ON';SETTime: 1.737 ms[local]:5432 pg12@testdb=# [local]:5432 pg12@testdb=# CREATE OR REPLACE PROCEDURE sp_test(i int) pg12@testdb-# ASpg12@testdb-# $$pg12@testdb$# declarepg12@testdb$#  v_id integer := 0;pg12@testdb$# begin pg12@testdb$# for i in 1..i looppg12@testdb$# pg12@testdb$# raise notice 'id is %',i;pg12@testdb$# end loop;pg12@testdb$# end;pg12@testdb$# $$  LANGUAGE plpgsql;CREATE PROCEDURETime: 4.077 ms[local]:5432 pg12@testdb=# [local]:5432 pg12@testdb=# call sp_test(100);NOTICE:  id is 1...[local]:5432 pg12@testdb=# select lineno,avg_time,source from plpgsql_profiler_function_tb('sp_test(int)'); lineno | avg_time |          source           --------+----------+---------------------------      1 |          |       2 |          | declare      3 |          |  v_id integer := 0;      4 |    0.192 | begin       5 |    0.248 | for i in 1..i loop      6 |    0.027 | raise notice 'id is %',i;      7 |          | end loop;      8 |          | end;(8 rows)Time: 1.872 ms

参考资料
plpgsql_check
Using plpgsql_check to Find Compilation Errors and Profile Functions