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.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
Using plpgsql_check to Find Compilation Errors and Profile Functions