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.spec expected plpgsql_check.so postgresql96-plpgsql_check.spec LICENSE postgresql10-plpgsql_check.spec README.md Makefile postgresql11-plpgsql_check.spec sql META.json postgresql12-plpgsql_check.spec src msvc postgresql13-plpgsql_check.spec TODO.md plpgsql_check--1.7.sql postgresql94-plpgsql_check.spec [pg12@localhost plpgsql_check]$ make ... [pg12@localhost plpgsql_check]$ sudo make install make -C ../../src/backend generated-headers make[1]: Entering directory `/data/source/postgresql-12beta1/src/backend' make -C catalog distprep generated-header-symlinks make[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-symlinks make[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.conf shared_preload_libraries = 'pg_stat_statements,pg_qualstats,plpgsql,plpgsql_check' # (change requires restart) [pg12@localhost pg12db1]$ pg_ctl restart waiting for server to shut down.... done server stopped waiting 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-bit 2019-08-09 12:07:00.243 CST [2086] LOG: listening on IPv4 address "0.0.0.0", port 5432 2019-08-09 12:07:00.243 CST [2086] LOG: listening on IPv6 address "::", port 5432 2019-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 process 2019-08-09 12:07:00.407 CST [2086] HINT: Future log output will appear in directory "pg_log". done server started ``` 创建extension ``` [local]:5432 pg12@testdb=# create extension plpgsql_check; CREATE EXTENSION Time: 235.761 ms ``` **语义检查** 数据表t_noexists不存在,但PG并没有执行语义检查 ``` [local]:5432 pg12@testdb=# CREATE OR REPLACE PROCEDURE sp_error(int) pg12@testdb-# AS pg12@testdb-# $$ pg12@testdb$# declare pg12@testdb$# v_id integer := 0; pg12@testdb$# begin pg12@testdb$# SELECT 1 FROM t_notexists; pg12@testdb$# end; pg12@testdb$# $$ LANGUAGE plpgsql; CREATE PROCEDURE Time: 2.265 ms [local]:5432 pg12@testdb=# call sp_error(1); ERROR: relation "t_notexists" does not exist LINE 1: SELECT 1 FROM t_notexists ^ QUERY: SELECT 1 FROM t_notexists CONTEXT: PL/pgSQL function sp_error(integer) line 5 at SQL statement Time: 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_error lineno | 5 statement | SQL statement sqlstate | 42P01 message | relation "t_notexists" does not exist detail | hint | level | error position | 15 query | SELECT 1 FROM t_notexists context | Time: 19.023 ms ``` *但对于未声明的变量,没有校验(下例中的x)或者错误的认为是列名(v_id1)* ``` [local]:5432 pg12@testdb=# CREATE OR REPLACE PROCEDURE sp_error2(int) pg12@testdb-# AS pg12@testdb-# $$ pg12@testdb$# declare pg12@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 PROCEDURE Time: 1.152 ms [local]:5432 pg12@testdb=# select * from plpgsql_check_function_tb('sp_error2(int)'); -[ RECORD 1 ]----------------------------- functionid | sp_error2 lineno | 5 statement | RAISE sqlstate | 42703 message | column "v_id1" does not exist detail | hint | level | error position | 8 query | SELECT v_id1 context | 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 INT pg12@testdb-# AS pg12@testdb-# $$ pg12@testdb$# declare pg12@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 FUNCTION Time: 4.135 ms [local]:5432 pg12@testdb=# [local]:5432 pg12@testdb=# CREATE OR REPLACE PROCEDURE sp_error3(int) pg12@testdb-# AS pg12@testdb-# $$ pg12@testdb$# declare pg12@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 PROCEDURE Time: 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'; SET Time: 1.737 ms [local]:5432 pg12@testdb=# [local]:5432 pg12@testdb=# CREATE OR REPLACE PROCEDURE sp_test(i int) pg12@testdb-# AS pg12@testdb-# $$ pg12@testdb$# declare pg12@testdb$# v_id integer := 0; pg12@testdb$# begin pg12@testdb$# for i in 1..i loop pg12@testdb$# pg12@testdb$# raise notice 'id is %',i; pg12@testdb$# end loop; pg12@testdb$# end; pg12@testdb$# $$ LANGUAGE plpgsql; CREATE PROCEDURE Time: 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](https://github.com/okbob/plpgsql_check) [Using plpgsql_check to Find Compilation Errors and Profile Functions ](https://www.percona.com/blog/2019/07/30/using-plpgsql_check-to-find-compilation-errors-and-profile-functions/)