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