Shulou(Shulou.com)06/01 Report--

PostgreSQL does not perform semantic analysis / verification when creating stored procedures / functions, which can be checked by the plug-in plpgsql_check. In addition, the plug-in can also find functional dependencies and analyze function performance.

This section provides a brief introduction to the installation and use of the extension.

Pplpgsql_check can accomplish the following three tasks:

1.Checking for compilation errors in a function code

2.Finding dependencies in functions

3.Profiling functions

Features includes:

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

Installation

Download the source code from 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.specs [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/'

The plpgsql_check plug-in needs to preload the dynamic link library and modify the postgresql.conf file

[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 LOG 07Riv 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 LOG 07VR 00.243 CST [2086] LOG: listening on IPv4 address "0.0.0.0", port 54322019-08-09 12V 07lane 00.243 CST [2086] LOG: listening on IPv6 address ":" Port 54322019-08-09 12 CST 00.256 CST [2086] LOG: listening on Unix socket "/ tmp/.s.PGSQL.5432" 2019-08-09 12 LOG 07VR 00.407 CST [2086] LOG: redirecting log output to logging collector process2019-08-09 12 purl 07VR 00.407 CST [2086] HINT: Future log output will appear in directory "pg_log". Doneserver started

Create extension

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

Semantic check

The datasheet t_noexists does not exist, but PG does not perform semantic checking

[local]: 5432 pg12@testdb=# CREATE OR REPLACE PROCEDURE sp_error (int) pg12@testdb-# ASpg12@testdb-# $$pg12@testdb$# declarepg12@testdb$# v_id integer: = 0 ms 12 LANGUAGE plpgsql;CREATE PROCEDURETime [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=#

Check for semantic errors through 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

However, for undeclared variables, there is no check (x in the following example) or mistakenly considered to be a column name (v_id1)

[local]: 5432 pg12@testdb=# CREATE OR REPLACE PROCEDURE sp_error2 (int) pg12@testdb-# ASpg12@testdb-# $$pg12@testdb$# declarepg12@testdb$# v_id integer: = 0locpg12perfect testdbstores # begin pg12@testdb$# raise notice'id is%', vSecretid1 + raise notice'id is%', xtterpg12diagnostic testdbposts # 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

Functional dependency

Through the plpgsql_show_dependency_tb function, you can query object (stored procedure / function, etc.) dependencies

[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: = 0locpg12complete testdbsites # begin pg12@testdb$# raise notice'id is%', vSecretid1 + raise notice'id is%', xtterpg12diagnostic testbundles # return 0id is # end;pg12@testdb$# $LANGUAGE plpgsql; integer: = 0begin raise notice'id is%', v_id1 Raise notice'id is%', x; select sp_func1 (v_id) into vastly successful candidates $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%', vSecretid1: pg12complete testdatabase # raise notice'id is%', xtinctpg12perfect testdatabase # select sp_func1 (v_id) into vSecretidentpg12complete testdatabase # 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

Performance analysis.

Enable performance analysis options, execute procedures, and query performance data

[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 looppg12@testdb$# pg12@testdb$# raise notice' id is%', I looppg12@testdb$# pg12@testdb$# raise notice'id is%' Pg12@testdb$# $$LANGUAGE plpgsql;CREATE PROCEDURETime: 4.077 ms [local]: 5432 pg12@testdb=# [local]: 5432 pg12@testdb=# call sp_test; 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

references

Plpgsql_check

Using plpgsql_check to Find Compilation Errors and Profile Functions

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.