溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
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
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
