plpgsql_check: PL/pgSQL 源码静态检查器

十二月 23, 2023

摘要：plpgsql_check插件是一个 linter 工具，用于对 PostgreSQL PL/pgSQL 语言源代码进行静态分析，该语言是 PostgreSQL 存储过程的原生语言。

目录

介绍

plpgsql_check插件是 PostgreSQL PL/pgSQL 的完整 linter。它仅利用内部的 PostgreSQL 解析器/评估器，因此您可以准确地看到运行时会发生的错误。此外，它还会解析函数/存储过程中的 SQL，并查找在“CREATE PROCEDURE/FUNCTION”命令中通常不会发现的错误。 您可以控制许多警告和提示的消息级别。最后，您可以添加 PRAGMA 类型的标记，来关闭/打开许多方面的检查，从而隐藏您已经知道的消息，或提醒您稍后再回来进行更深入的清理。

特性

  • 检查内部 SQL 语句中引用的数据库对象字段和类型
  • 验证函数参数是否使用了正确的类型
  • 识别未使用到的变量、函数参数，以及未修改的 OUT 参数
  • 尝试检测存在的死代码（RETURN 命令后的代码）
  • 检测函数中 RETURN 命令的缺失（常见于异常处理之后，逻辑复杂的时候）
  • 尝试识别不需要的隐式转换，这可能是一个性能问题，例如未使用到索引
  • 能够收集函数使用到的关系和函数
  • 能够检查 EXECUTE 语句是否存在 SQL 注入漏洞

验证器会检查 PL/pgSQL 函数中的 SQL 语句，报告存在的语义错误。可以通过调用plpgsql_check_function找到这些错误：

主动模式

postgres=# CREATE EXTENSION plpgsql_check;
LOAD
postgres=# CREATE TABLE t1(a int, b int);
CREATE TABLE

postgres=#
CREATE OR REPLACE FUNCTION public.f1()
RETURNS void
LANGUAGE plpgsql
AS $function$
DECLARE r record;
BEGIN
  FOR r IN SELECT * FROM t1
  LOOP
    RAISE NOTICE '%', r.c; -- there is bug - table t1 missing "c" column
  END LOOP;
END;
$function$;

CREATE FUNCTION

postgres=# select f1(); -- execution doesn't find a bug due to empty table t1
  f1 
 -----
   
 (1 row)

postgres=# \x
Expanded display is on.
postgres=# select * from plpgsql_check_function_tb('f1()');
-[ RECORD 1 ]---------------------------
functionid | f1
lineno     | 6
statement  | RAISE
sqlstate   | 42703
message    | record "r" has no field "c"
detail     | [null]
hint       | [null]
level      | error
position   | 0
query      | [null]

postgres=# \sf+ f1
    CREATE OR REPLACE FUNCTION public.f1()
     RETURNS void
     LANGUAGE plpgsql
1       AS $function$
2       DECLARE r record;
3       BEGIN
4         FOR r IN SELECT * FROM t1
5         LOOP
6           RAISE NOTICE '%', r.c; -- there is bug - table t1 missing "c" column
7         END LOOP;
8       END;
9       $function$

函数plpgsql_check_function()有三种可能的输出格式：text、json 或 xml

select * from plpgsql_check_function('f1()', fatal_errors := false);
                         plpgsql_check_function                         
------------------------------------------------------------------------
 error:42703:4:SQL statement:column "c" of relation "t1" does not exist
 Query: update t1 set c = 30
 --                   ^
 error:42P01:7:RAISE:missing FROM-clause entry for table "r"
 Query: SELECT r.c
 --            ^
 error:42601:7:RAISE:too few parameters specified for RAISE
(7 rows)

postgres=# select * from plpgsql_check_function('fx()', format:='xml');
                 plpgsql_check_function
----------------------------------------------------------------
 <Function oid="16400">
   <Issue>
     <Level>error</level>
     <Sqlstate>42P01</Sqlstate>
     <Message>relation "foo111" does not exist</Message>
     <Stmt lineno="3">RETURN</Stmt>
     <Query position="23">SELECT (select a from foo111)</Query>
   </Issue>
  </Function>
 (1 row)

参数

您可以通过函数参数设置警告级别：

强制性参数

  • funcoid oid - 函数名称或函数签名 - 函数需要有函数规范。 PostgreSQL 中的任何函数都可以由 Oid、名称或签名指定。当您知道 oid 或完整的函数签名时，可以使用 regprocedure 类型参数，如'fx()'::regprocedure或16799::regprocedure。可能的替代方法是，当函数的名称是唯一的时候，可以仅使用名称 - 例如'fx'。但是，当名称不唯一或函数不存在时，这样会引发错误。

可选参数

  • relid DEFAULT 0 - 触发器函数关联的关系的 OID。有必要检查任何触发器函数。通过该参数可传入触发器操作的表。

  • fatal_errors boolean DEFAULT true - 在发现第一个错误时停止（防止报告大量错误信息）

  • other_warnings boolean DEFAULT true - 显示警告信息，例如在赋值表达式两侧使用了不同的属性编号、变量名覆盖了函数的参数、未使用到的变量、不需要的强制转换等。

  • extra_warnings boolean DEFAULT true - 显示警告信息，如RETURN语句缺失、影子变量、死代码、从未读取（未使用到）的函数参数、未修改的变量、修改过的自动变量等。

  • performance_warnings boolean DEFAULT false - 性能相关的警告信息，例如带有类型修饰符的声明类型、强制转换、where 子句中的隐式转换（这可能是未使用到索引的原因）等。

  • security_warnings boolean DEFAULT false - 安全相关的检查，如 SQL 注入漏洞检测。

  • compatibility_warnings boolean DEFAULT false - 兼容性相关的检查，例如在 refcursor 或 cursor 变量中，显式设置过时的内部游标名称。

  • anyelementtype regtype DEFAULT 'int' - 测试 anyelement 类型时要使用的实际类型。

  • anyenumtype regtype DEFAULT '-' - 测试 anyenum 类型时要使用的实际类型。

  • anyrangetype regtype DEFAULT 'int4range' - 测试 anyrange 类型时要使用的实际类型。

  • anycompatibletype DEFAULT 'int' - 测试 anycompatible 类型时要使用的实际类型。

  • anycompatiblerangetype DEFAULT 'int4range' - 测试 anycompatible range 类型时要使用的实际 range 类型。

  • without_warnings DEFAULT false - 禁用所有警告（忽略所有 xxx_warning 参数，这是一种快速覆盖的方式）

  • all_warnings DEFAULT false - 启用所有警告（忽略其他 xxx_warning 参数，这是一种快速开启的方式）

  • newtable DEFAULT NULL, oldtable DEFAULT NULL - NEW 或 OLD 转换表的名称。在触发器函数中使用转换表时，这些参数是必需的。

  • use_incomment_options DEFAULT true - 如果为 true，则注释内选项会处于启用状态。

  • incomment_options_usage_warning DEFAULT false - 如果为 true，则在使用注释内选项时会输出警告。

  • constant_tracing boolean DEFAULT true - 当它为 true 时，那些存放常量内容的变量，可以像常量一样使用 （这只在一些简单的情况下起作用，且在这些情况下，变量的内容不应该模棱两可）。

触发器

当您想检查任何触发器时，您必须输入一个将与触发器函数一起使用的关系

CREATE TABLE bar(a int, b int);

postgres=# \sf+ foo_trg
    CREATE OR REPLACE FUNCTION public.foo_trg()
         RETURNS trigger
         LANGUAGE plpgsql
1       AS $function$
2       BEGIN
3         NEW.c := NEW.a + NEW.b;
4         RETURN NEW;
5       END;
6       $function$

缺少关系检查时

postgres=# select * from plpgsql_check_function('foo_trg()');
ERROR:  missing trigger relation
HINT:  Trigger relation oid must be valid

正确的触发器函数检查（带有指定的关系）

postgres=# select * from plpgsql_check_function('foo_trg()', 'bar');
                 plpgsql_check_function
--------------------------------------------------------
 error:42703:3:assignment:record "new" has no field "c"
(1 row)

对于带有传递表的触发器，可以设置oldtable和newtable参数：

create or replace function footab_trig_func()
returns trigger as $$
declare x int;
begin
  if false then
    -- should be ok;
    select count(*) from newtab into x; 

    -- should fail;
    select count(*) from newtab where d = 10 into x;
  end if;
  return null;
end;
$$ language plpgsql;

select * from plpgsql_check_function('footab_trig_func','footab', newtable := 'newtab');

注释内选项

plpgsql_check允许在注释中保存检查的设置。它在检查之前会从函数的源代码获取这些选项。语法为：

@plpgsql_check_option: optioname [=] value [, optname [=] value ...]

注释内选项进行的设置具有最高优先级，但通常可以通过设置选项use_incomment_options为false来禁用它。

示例：

create or replace function fx(anyelement)
returns text as $$
begin
  /*
   * rewrite default polymorphic type to text
   * @plpgsql_check_options: anyelementtype = text
   */
  return $1;
end;
$$ language plpgsql;

检查所有代码

您可以使用plpgsql_check_function，对函数/存储过程进行批量检查，也可以对触发器进行批量检查。请测试以下查询：

-- check all nontrigger plpgsql functions
SELECT p.oid, p.proname, plpgsql_check_function(p.oid)
   FROM pg_catalog.pg_namespace n
   JOIN pg_catalog.pg_proc p ON pronamespace = n.oid
   JOIN pg_catalog.pg_language l ON p.prolang = l.oid
  WHERE l.lanname = 'plpgsql' AND p.prorettype <> 2279;

或

-- check all trigger plpgsql functions
SELECT p.proname, tgrelid::regclass, cf.*
   FROM pg_proc p
        JOIN pg_trigger t ON t.tgfoid = p.oid 
        JOIN pg_language l ON p.prolang = l.oid
        JOIN pg_namespace n ON p.pronamespace = n.oid,
        LATERAL plpgsql_check_function(p.oid, t.tgrelid) cf
  WHERE n.nspname = 'public' and l.lanname = 'plpgsql';

或

-- check all plpgsql functions (functions or trigger functions with defined triggers)
SELECT
    (pcf).functionid::regprocedure, (pcf).lineno, (pcf).statement,
    (pcf).sqlstate, (pcf).message, (pcf).detail, (pcf).hint, (pcf).level,
    (pcf)."position", (pcf).query, (pcf).context
FROM
(
    SELECT
        plpgsql_check_function_tb(pg_proc.oid, COALESCE(pg_trigger.tgrelid, 0)) AS pcf
    FROM pg_proc
    LEFT JOIN pg_trigger
        ON (pg_trigger.tgfoid = pg_proc.oid)
    WHERE
        prolang = (SELECT lang.oid FROM pg_language lang WHERE lang.lanname = 'plpgsql') AND
        pronamespace <> (SELECT nsp.oid FROM pg_namespace nsp WHERE nsp.nspname = 'pg_catalog') AND
        -- ignore unused triggers
        (pg_proc.prorettype <> (SELECT typ.oid FROM pg_type typ WHERE typ.typname = 'trigger') OR
         pg_trigger.tgfoid IS NOT NULL)
    OFFSET 0
) ss
ORDER BY (pcf).functionid::regprocedure::text, (pcf).lineno;

被动模式

警告：仅建议用于开发或预生产环境。

可以在执行时检查函数 - 必须加载了plpgsql_check模块（通过 postgresql.conf）。

配置设置

plpgsql_check.mode = [ disabled | by_function | fresh_start | every_start ]
plpgsql_check.fatal_errors = [ yes | no ]

plpgsql_check.show_nonperformance_warnings = false
plpgsql_check.show_performance_warnings = false

缺省模式是by_function，这意味着增强检查仅在主动模式下完成 - 通过调用plpgsql_check_function的方式。fresh_start表示冷启动（首先调用函数）。

您可以通过以下方式启用被动模式

load 'plpgsql'; -- 1.1 and higher doesn't need it
load 'plpgsql_check';
set plpgsql_check.mode = 'every_start';  -- This scans all code before it is executed

SELECT fx(10); -- run functions - function is checked before runtime starts it

兼容性警告

将字符串赋值给 refcursor 变量

PostgreSQL 的 cursor 和 refcursor 变量是增强的字符串变量，具有相关 portal（Postgres 的一个内部结构，用于游标的实现）的唯一名称。在 PostgreSQL 16 之前，portal 的名称与游标变量的名称相同。PostgreSQL 16 及更高版本更改了此机制，默认情况下，相关 portal 将以某个唯一名称命名。它解决了嵌套代码块中游标的一些问题，或者在递归调用函数中使用游标时的一些问题。

按照上述更改，refcursor 的变量应从另一个 refcursor 变量或某个 cursor 变量（当游标打开时）获取值。

-- obsolete pattern
DECLARE
  cur CURSOR FOR SELECT 1;
  rcur refcursor;
BEGIN
  rcur := 'cur';
  OPEN cur;
  ...

-- new pattern
DECLARE
  cur CURSOR FOR SELECT 1;
  rcur refcursor;
BEGIN
  OPEN cur;
  rcur := cur;
  ...

当compatibility_warnings标记处于启用状态时，plpgsql_check会尝试识别，给 refcursor 变量进行的一些可疑的赋值，或者返回 refcursor 值的情况：

CREATE OR REPLACE FUNCTION public.foo()
 RETURNS refcursor
AS $$
declare
   c cursor for select 1;
   r refcursor;
begin
  open c;
  r := 'c';
  return r;
end;
$$ LANGUAGE plpgsql;

select * from plpgsql_check_function('foo', extra_warnings =>false, compatibility_warnings => true);

                              plpgsql_check_function
-----------------------------------------------------------------------------------
 compatibility:00000:6:assignment:obsolete setting of refcursor or cursor variable
 Detail: Internal name of cursor should not be specified by users.
 Context: at assignment to variable "r" declared on line 3
(3 rows)

限制

在真正的静态代码上，plpgsql_check应该会找到几乎所有错误。当开发人员在使用 PL/pgSQL 的动态功能（如动态 SQL 或 record 数据类型）时，可能会出现误报。在编写良好的代码中，这些应该很少见，然后受影响的函数应该进行重新设计，或者应该对此函数禁用plpgsql_check。

CREATE OR REPLACE FUNCTION f1()
RETURNS void AS $$
DECLARE r record;
BEGIN
  FOR r IN EXECUTE 'SELECT * FROM t1'
  LOOP
    RAISE NOTICE '%', r.c;
  END LOOP;
END;
$$ LANGUAGE plpgsql SET plpgsql.enable_check TO false;

注意：使用 plpgsql_check 会增加少量开销（在启用被动模式时），您应该仅在开发或预生产环境中使用该设置。

动态 SQL

该模块不会检查在运行时组装的查询。它无法识别动态查询的结果，因此plpgsql_check无法设置正确的类型来记录变量，也无法检查依赖的 SQL 和表达式。

当 record 变量的类型未知时，可以使用检查指示type显式指定它：

DECLARE r record;
BEGIN
  EXECUTE format('SELECT * FROM %I', _tablename) INTO r;
  PERFORM plpgsql_check_pragma('type: r (id int, processed bool)');
  IF NOT r.processed THEN
    ...

注意：SQL 注入检查只能检测部分 SQL 注入漏洞。此工具不能用于安全审计！某些问题会无法检测到。此检查也可能引发误报 - 可能是当变量被其他命令擦除，或者该值为某种复合类型时。

引用游标

plpgsql_check不能用于检测引用游标的结构。PL/pgSQL 中对游标的引用被实现为全局游标的名称。在检查时，名称是未知的（并非所有可能的情况），并且全局游标是不存在的。对于任何静态分析来说，这都是一个重要问题。PL/pgSQL 不知道如何为 record 变量设置正确的类型，也无法检查依赖的 SQL 语句和表达式。对于动态 SQL，解决方案是相同的。在使用refcursor类型时，请勿使用 record 变量作为赋值表达式中的目标变量，如果确实需要，可以对这些函数禁用plpgsql_check。

CREATE OR REPLACE FUNCTION foo(refcur_var refcursor)
RETURNS void AS $$
DECLARE
  rec_var record;
BEGIN
  FETCH refcur_var INTO rec_var; -- this is STOP for plpgsql_check
  RAISE NOTICE '%', rec_var;     -- record rec_var is not assigned yet error

在这种情况下，不应使用 record 类型（请改用已知的行类型）：

CREATE OR REPLACE FUNCTION foo(refcur_var refcursor)
RETURNS void AS $$
DECLARE
  rec_var some_rowtype;
BEGIN
  FETCH refcur_var INTO rec_var;
  RAISE NOTICE '%', rec_var;

临时表

plpgsql_check无法验证在 plpgsql 函数运行时创建的临时表上的查询。对于这种场景，有必要创建一个假的临时表，或者对函数禁用plpgsql_check。

实际上，临时表存储在（每个用户）自己的模式中，其优先级高于普通表。因此，您可以安全地使用下面的方法进行操作：

CREATE OR REPLACE FUNCTION public.disable_dml()
RETURNS trigger
LANGUAGE plpgsql AS $function$
BEGIN
  RAISE EXCEPTION SQLSTATE '42P01'
     USING message = format('this instance of %I table doesn''t allow any DML operation', TG_TABLE_NAME),
           hint = format('you should use "CREATE TEMP TABLE %1$I(LIKE %1$I INCLUDING ALL);" statement',
                         TG_TABLE_NAME);
  RETURN NULL;
END;
$function$;

CREATE TABLE foo(a int, b int); -- doesn't hold data, ever
CREATE TRIGGER foo_disable_dml
   BEFORE INSERT OR UPDATE OR DELETE ON foo
   EXECUTE PROCEDURE disable_dml();

postgres=# INSERT INTO  foo VALUES(10,20);
ERROR:  this instance of foo table doesn't allow any DML operation
HINT:  you should to run "CREATE TEMP TABLE foo(LIKE foo INCLUDING ALL);" statement
postgres=# 

CREATE TABLE
postgres=# INSERT INTO  foo VALUES(10,20);
INSERT 0 1

该方法部分模拟了 GLOBAL TEMP 表，这样就可以进行静态的验证。

您可以使用检查指示table，来创建临时使用的表：

BEGIN
   CREATE TEMP TABLE xxx(a int);
   PERFORM plpgsql_check_pragma('table: xxx(a int)');
   INSERT INTO xxx VALUES(10);
   PERFORM plpgsql_check_pragma('table: [pg_temp].zzz(like schemaname.table1 including all)');
   ...

依赖项列表

函数plpgsql_show_dependency_tb可以显示，处理的函数内部使用到的所有函数、运算符和关系：

postgres=# select * from plpgsql_show_dependency_tb('testfunc(int,float)');
   type   |  oid  | schema |  name   |           params
----------|-------|--------|---------|----------------------------
 FUNCTION | 36008 | public | myfunc1 | (integer,double precision)
 FUNCTION | 35999 | public | myfunc2 | (integer,double precision)
 OPERATOR | 36007 | public | **      | (integer,integer)
 RELATION | 36005 | public | myview  |
 RELATION | 36002 | public | mytable |
(4 rows)

plpgsql_show_dependency_tb的可选参数有：relid、anyelementtype、enumtype、anyrangetype、anycompatibletype和anycompatiblerangetype。

分析器

plpgsql_check包含了一个简单的 PL/pgSQL 函数和存储过程的分析器。在可以或者不能访问共享内存的情况下，它都可以工作。这取决于shared_preload_libraries配置。当plpgsql_check通过shared_preload_libraries初始化时，它可以分配共享内存，并将函数的概要信息存储在那里。当plpgsql_check无法分配共享内存时，函数概要信息将会存储在会话内存中。

由于依赖关系，shared_preload_libraries应该先包含plpgsql。

postgres=# show shared_preload_libraries;
 shared_preload_libraries
--------------------------
 plpgsql,plpgsql_check
(1 row)

当 GUC 参数plpgsql_check.profiler打开时，分析器处于启用状态。分析器不需要共享内存，但如果没有足够的共享内存，则分析器仅限于活跃会话。分析器可以通过调用函数plpgsql_check_profiler(true)来激活，也可以通过使用false参数（或者on、off）调用相同的函数来禁用。

当通过shared_preload_libraries初始化plpgsql_check时，另一个 GUC 参数可用于配置分析器使用的共享内存量：plpgsql_check.profiler_max_shared_chunks。这定义了共享内存中可以存储的最大语句块数。对于每个 PL/pgSQL 函数（或存储过程），整个内容被拆分为 30 个语句的块。如果需要，可以使用多个块来存储单个函数的全部内容。单个块为 1704 字节。这个 GUC 参数的默认值是 15000，这对于在 plpgsql 中包含数十万条语句的大型项目来说应该足够了，并且会消耗大约 24MB 的内存。如果您的项目不需要那么多块，您可以将此参数设置为较小的数字，以减少内存使用量。最小值为 50（会消耗大约 83kB 的内存），最大值为 100000（会消耗大约 163MB 的内存）。更改此参数需要重新启动 PostgreSQL 数据库服务。

分析器还会为包含表达式或可优化语句的每条指令检索查询标识符。请注意，这需要安装pg_stat_statements或其他类似的第三方插件。查询标识符的检索存在一些限制：

  • 如果一个 PL/pgSQL 表达式包含了下层的语句，则只会检索顶层查询的标识符。
  • 分析器本身不会计算查询标识符，而是依赖于外部插件（如pg_stat_statements）来实现。这意味着，根据外部插件的行为，您可能无法看到某些语句的查询标识符。例如，DDL 语句就是这种情况，因为pg_stat_statements不会提供此类查询的查询标识符。
  • 只有包含表达式的指令，才会检索查询标识符。这意味着在单行代码中，plpgsql_profiler_function_tb()函数报告的查询标识符会比指令少。

注意：在负载较高的服务器上，更新共享的概要信息，可能会降低数据库性能。

概要信息可以使用函数plpgsql_profiler_function_tb显示：

postgres=# select lineno, avg_time, source from plpgsql_profiler_function_tb('fx(int)');
 lineno | avg_time |                              source
--------|----------|-------------------------------------------------------------------
      1 |          |
      2 |          | declare result int = 0;
      3 |    0.075 | begin
      4 |    0.202 |   for i in 1..$1 loop
      5 |    0.005 |     select result + i into result; select result + i into result;
      6 |          |   end loop;
      7 |        0 |   return result;
      8 |          | end;
(9 rows)

结果中的时间以毫秒为单位。

每个语句（不是每行）的概要信息，可以通过函数plpgsql_profiler_function_statements_tb来显示：

        CREATE OR REPLACE FUNCTION public.fx1(a integer)
         RETURNS integer
         LANGUAGE plpgsql
1       AS $function$
2       begin
3         if a > 10 then
4           raise notice 'ahoj';
5           return -1;
6         else
7           raise notice 'nazdar';
8           return 1;
9         end if;
10      end;
11      $function$

postgres=# select stmtid, parent_stmtid, parent_note, lineno, exec_stmts, stmtname
             from plpgsql_profiler_function_statements_tb('fx1');
 stmtid | parent_stmtid | parent_note | lineno | exec_stmts |    stmtname
--------|---------------|-------------|--------|------------|-----------------
      0 |             ∅ | ∅           |      2 |          0 | statement block
      1 |             0 | body        |      3 |          0 | IF
      2 |             1 | then body   |      4 |          0 | RAISE
      3 |             1 | then body   |      5 |          0 | RETURN
      4 |             1 | else body   |      7 |          0 | RAISE
      5 |             1 | else body   |      8 |          0 | RETURN
(6 rows)

所有存储的概要信息，可以通过调用函数plpgsql_profiler_functions_all来显示：

postgres=# select * from plpgsql_profiler_functions_all();
        funcoid        | exec_count | total_time | avg_time | stddev_time | min_time | max_time
-----------------------|------------|------------|----------|-------------|----------|----------
 fxx(double precision) |          1 |       0.01 |     0.01 |        0.00 |     0.01 |     0.01
(1 row)

有两个函数可用于清理存储的概要信息：plpgsql_profiler_reset_all()和plpgsql_profiler_reset(regprocedure)。

覆盖率指标

plpgsql_check提供了两个函数：

  • plpgsql_coverage_statements(name)
  • plpgsql_coverage_branches(name)

备注

还有一个非常好用的 PL/pgSQL 分析器 - https://github.com/glynastill/plprofiler

plpgsql_check的分析器设计简单易用且实用。仅此而已。

plprofiler 更加复杂。它会构建调用图，并从此图可以创建执行时间的火焰图。

这两个插件都可以与 PostgreSQL 内置的功能（跟踪函数）一起使用。

set track_functions to 'pl';
...
select * from pg_stat_user_functions;

追踪器

plpgsql_check提供了一种追踪的可能性 - 在此模式下，您可以看到有关开始或结束函数（简洁和默认的输出模式下）的通知，以及开始或结束语句（详细的输出模式下）的通知。对于默认和详细的输出模式，会显示出函数参数的内容。在详细的输出模式下，会显示相关变量的内容。

postgres=# do $$ begin perform fx(10,null, 'now', e'stěhule'); end; $$;
NOTICE:  #0 ->> start of inline_code_block (Oid=0)
NOTICE:  #2   ->> start of function fx(integer,integer,date,text) (Oid=16405)
NOTICE:  #2        call by inline_code_block line 1 at PERFORM
NOTICE:  #2       "a" => '10', "b" => null, "c" => '2020-08-03', "d" => 'stěhule'
NOTICE:  #4     ->> start of function fx(integer) (Oid=16404)
NOTICE:  #4          call by fx(integer,integer,date,text) line 1 at PERFORM
NOTICE:  #4         "a" => '10'
NOTICE:  #4     <<- end of function fx (elapsed time=0.098 ms)
NOTICE:  #2   <<- end of function fx (elapsed time=0.399 ms)
NOTICE:  #0 <<- end of block (elapsed time=0.754 ms)

#后面的数字是执行栈帧的计数器，此数字与错误上下文堆栈的深度有关。它允许函数的开始和结束配对显示。

通过将plpgsql_check.tracer设置为on可以启用追踪。注意 - 启用此行为会对性能产生重大负面影响（这与分析器不同）。您可以为追踪器使用到的plpgsql_check.tracer_errlevel参数，设置输出级别（默认值为notice）。输出内容受plpgsql_check.tracer_variable_max_length配置变量指定的长度限制。追踪器可以通过调用函数plpgsql_check_tracer(true)来激活，也可以通过使用false参数（或着on、off）调用相同的函数来禁用。

首先，超级用户应该通过执行set plpgsql_check.enable_tracer to on;，或者在postgresql.conf中添加plpgsql_check.enable_tracer = on，来显式地启用追踪器。这是一项安全保障措施。追踪器会显示 plpgsql 变量的内容，然后可以向非特权用户显示一些安全敏感信息（在他运行安全性定义者函数时）。其次，应该加载插件plpgsql_check。它可以通过执行某些plpgsql_check函数完成，也可以通过命令load 'plpgsql_check';显式完成。您可以使用配置选项shared_preload_libraries、local_preload_libraries或者session_preload_libraries。

在简洁输出的模式下，输出会减少：

postgres=# set plpgsql_check.tracer_verbosity TO terse;
SET
postgres=# do $$ begin perform fx(10,null, 'now', e'stěhule'); end; $$;
NOTICE:  #0 start of inline code block (oid=0)
NOTICE:  #2 start of fx (oid=16405)
NOTICE:  #4 start of fx (oid=16404)
NOTICE:  #4 end of fx
NOTICE:  #2 end of fx
NOTICE:  #0 end of inline code block

在详细输出的模式下，输出增加了有关语句的详细信息：

postgres=# do $$ begin perform fx(10,null, 'now', e'stěhule'); end; $$;
NOTICE:  #0            ->> start of block inline_code_block (oid=0)
NOTICE:  #0.1       1  --> start of PERFORM
NOTICE:  #2              ->> start of function fx(integer,integer,date,text) (oid=16405)
NOTICE:  #2                   call by inline_code_block line 1 at PERFORM
NOTICE:  #2                  "a" => '10', "b" => null, "c" => '2020-08-04', "d" => 'stěhule'
NOTICE:  #2.1       1    --> start of PERFORM
NOTICE:  #2.1                "a" => '10'
NOTICE:  #4                ->> start of function fx(integer) (oid=16404)
NOTICE:  #4                     call by fx(integer,integer,date,text) line 1 at PERFORM
NOTICE:  #4                    "a" => '10'
NOTICE:  #4.1       6      --> start of assignment
NOTICE:  #4.1                  "a" => '10', "b" => '20'
NOTICE:  #4.1              <-- end of assignment (elapsed time=0.076 ms)
NOTICE:  #4.1                  "res" => '130'
NOTICE:  #4.2       7      --> start of RETURN
NOTICE:  #4.2                  "res" => '130'
NOTICE:  #4.2              <-- end of RETURN (elapsed time=0.054 ms)
NOTICE:  #4                <<- end of function fx (elapsed time=0.373 ms)
NOTICE:  #2.1            <-- end of PERFORM (elapsed time=0.589 ms)
NOTICE:  #2              <<- end of function fx (elapsed time=0.727 ms)
NOTICE:  #0.1          <-- end of PERFORM (elapsed time=1.147 ms)
NOTICE:  #0            <<- end of block (elapsed time=1.286 ms)

追踪器的一个特殊功能是，在将plpgsql_check.trace_assert设置为on时，可以跟踪ASSERT语句。在plpgsql_check.trace_assert_verbosity设置为DEFAULT时，当断言表达式结果为 false 时，会显示函数或存储过程中的相关变量。 在该配置设置为VERBOSE时，所有 plpgsql 栈帧中的所有变量都会显示。这种行为与plpgsql.check_asserts参数的值无关。尽管断言在 plpgsql 运行时被禁用，但可以使用它。

postgres=# set plpgsql_check.tracer to off;
postgres=# set plpgsql_check.trace_assert_verbosity TO verbose;

postgres=# do $$ begin perform fx(10,null, 'now', e'stěhule'); end; $$;
NOTICE:  #4 PLpgSQL assert expression (false) on line 12 of fx(integer) is false
NOTICE:   "a" => '10', "res" => null, "b" => '20'
NOTICE:  #2 PL/pgSQL function fx(integer,integer,date,text) line 1 at PERFORM
NOTICE:   "a" => '10', "b" => null, "c" => '2020-08-05', "d" => 'stěhule'
NOTICE:  #0 PL/pgSQL function inline_code_block line 1 at PERFORM
ERROR:  assertion failed
CONTEXT:  PL/pgSQL function fx(integer) line 12 at ASSERT
SQL statement "SELECT fx(a)"
PL/pgSQL function fx(integer,integer,date,text) line 1 at PERFORM
SQL statement "SELECT fx(10,null, 'now', e'stěhule')"
PL/pgSQL function inline_code_block line 1 at PERFORM

postgres=# set plpgsql.check_asserts to off;
SET
postgres=# do $$ begin perform fx(10,null, 'now', e'stěhule'); end; $$;
NOTICE:  #4 PLpgSQL assert expression (false) on line 12 of fx(integer) is false
NOTICE:   "a" => '10', "res" => null, "b" => '20'
NOTICE:  #2 PL/pgSQL function fx(integer,integer,date,text) line 1 at PERFORM
NOTICE:   "a" => '10', "b" => null, "c" => '2020-08-05', "d" => 'stěhule'
NOTICE:  #0 PL/pgSQL function inline_code_block line 1 at PERFORM
DO

追踪器可以显示子事务缓冲区 ID（nxids）的使用情况。显示的tnl数字是事务嵌套层级数（对于 plpgsql，它取决于带有异常处理的代码块的深度）。

检测未关闭的游标

PL/pgSQL 中的游标只是 SQL 游标的名称。SQL 游标的生命周期并没有关联到相关 plpgsql 游标变量的作用域。SQL 游标在事务结束时会被自己关闭，这对于较长的事务和过多打开的游标，可能会有点迟。当不需要游标时，最好显式地关闭游标（通过 CLOSE 语句）。不显式关闭的话，可能会出现严重的内存问题。

当 OPEN 语句尝试使用尚未关闭的游标时，会引发警告。可以通过设置plpgsql_check.cursors_leaks为off来禁用此功能。当函数被递归地调用时，该检查处于关闭状态。

函数完成后，可以立即检查未关闭的游标。默认情况下，该检查处于关闭状态，应该通过设置plpgsql_check.strict_cursors_leaks为on来启用检查。

任何未关闭的游标都只会报告一次。

结合使用 plugin_debugger

如果您将plugin_debugger（plpgsql 调试器）与plpgsql_check一起使用，则应在plugin_debugger之后初始化plpgsql_check（因为plugin_debugger不支持共享 PL/pgSQL 的调试 API）。例如，可以这样配置postgresql.conf：

shared_preload_libraries = 'plugin_debugger,plpgsql,plpgsql_check'

注意 - 安全

追踪器会输出变量或函数参数的内容。对于安全性定义者函数，这些内容可能包含安全敏感数据。这就是为什么默认情况下要禁用追踪器，并且只能使用超级用户权限启用plpgsql_check.enable_tracer的原因。

检查指示

您可以在检查的函数中，使用“pragma”函数配置plpgsql_check的行为。这是 PL/SQL 或 ADA 语言中 PRAGMA 功能的类比。PL/pgSQL 不支持 PRAGMA，但plpgsql_check会检测名为plpgsql_check_pragma的函数，并从此函数的参数中获取选项。这些plpgsql_check选项，直到这组语句的末尾都是有效的。

CREATE OR REPLACE FUNCTION test()
RETURNS void AS $$
BEGIN
  ...
  -- for following statements disable check
  PERFORM plpgsql_check_pragma('disable:check');
  ...
  -- enable check again
  PERFORM plpgsql_check_pragma('enable:check');
  ...
END;
$$ LANGUAGE plpgsql;

函数plpgsql_check_pragma是一个总是返回 1 的不可变函数。它由plpgsql_check插件定义。您可以声明替代的plpgsql_check_pragma函数，例如：

CREATE OR REPLACE FUNCTION plpgsql_check_pragma(VARIADIC args[])
RETURNS int AS $$
SELECT 1
$$ LANGUAGE sql IMMUTABLE;

在顶部代码块的声明部分使用 pragma 函数，也可以设置函数级别的选项。

CREATE OR REPLACE FUNCTION test()
RETURNS void AS $$
DECLARE
  aux int := plpgsql_check_pragma('disable:extra_warnings');
  ...

也支持更短的检查指示语法：

CREATE OR REPLACE FUNCTION test()
RETURNS void AS $$
DECLARE r record;
BEGIN
  PERFORM 'PRAGMA:TYPE:r (a int, b int)';
  PERFORM 'PRAGMA:TABLE: x (like pg_class)';
  ...

支持的检查指示

  • echo:str - 输出字符串（用于测试）。在字符串内部，可以使用“变量”：@@id、@@name、@@signature

  • status:check,status:tracer, status:other_warnings, status:performance_warnings, status:extra_warnings,status:security_warnings 这将输出当前值（例如other_warnings启用时）

  • enable:check,enable:tracer, enable:other_warnings, enable:performance_warnings, enable:extra_warnings,enable:security_warnings

  • disable:check,disable:tracer, disable:other_warnings, disable:performance_warnings, disable:extra_warnings,disable:security_warnings 这可用于从 anyelement 函数返回时禁用提示。只需将检查指示放在 RETURN 语句之前即可。

  • type:varname typename 或 type:varname (fieldname type, ...) - 设置 record 类型变量的实际类型

  • table: name (column_name type, ...) 或 table: name (like tablename) - 创建临时使用的临时表（如果要指定模式，则只允许使用pg_temp模式）。

  • sequence: name - 创建临时使用的临时序列

  • assert-schema: varname - 检查时的断言 - 确保变量指定的模式有效

  • assert-table: [ varname_schema, ] , varname - 确保变量（通过常量跟踪）指定的表名有效

  • assert-column: [varname_schema, ], varname_table , varname - 确保变量指定的列有效

检查指示enable:tracer和disable:tracer，只在 PostgreSQL 12 及更高版本才处于启用状态。

更新

plpgsql_check不支持版本的更新。在安装该插件的新版本之前，您应该先删除它。

搜索

分类

标签