H.5. plpgsql_check — статический анализ кода для PL/pgSQL
Расширение представляет собой инструмент для проверки кода, написанного на PL/pgSQL, для Postgres Pro. В ходе проверки используется только внутренний анализатор запросов Postgres Pro, что позволяет увидеть ошибки, которые возникнут при выполнении запроса. Расширение также анализирует SQL-синтаксис, использованный в подпрограммах, и выявляет ошибки, которые, как правило, пропускаются при выполнении команды CREATE PROCEDURE/FUNCTION. Можно настроить отображение предупреждений и подсказок. Кроме того, поддерживаются указания типа PRAGMA, с помощью которых, например, можно скрыть сообщения об уже известных ошибках или настроить напоминания, чтобы вернуться к какому-то фрагменту позже.
Расширение plpgsql_check поставляется вместе с Postgres Pro Enterprise в отдельном пакете plpgsql-check-ent-15 (подробные инструкции по установке приведены в Главе 17).
H.5.1. Функциональные возможности
Проверяет поля объектов базы данных, на которые даётся ссылка, и типы данных во встроенном SQL-коде.
Проверяет, что для параметров функций используются правильные типы данных.
Выявляет неиспользованные переменные и аргументы функций, а также неизменяемые аргументы OUT.
Обнаруживает некоторые части кода, которые не используются (код после команды
RETURN).Находит места в функции, где пропущена команда
RETURN(как правило, после обработки исключений или при сложной логике запроса).Пытается выявить нежелательные скрытые приведения типов, которые могут ухудшать производительность, например приводить к неиспользованию индексов.
Предоставляет возможность собирать информацию об отношениях и функциях, которые используются функцией.
Может проверять операторы
EXECUTEна уязвимость SQL-инъекций.
H.5.2. Использование
Модуль проверки исследует SQL-операторы в функциях PL/pgSQL на предмет семантических ошибок. Для выявления таких ошибок можно использовать функцию plpgsql_check_function. Чтобы посмотреть список аргументов функции, обратитесь к Подразделу H.5.3. Единственный обязательный аргумент — funcoid. Все остальные аргументы, перечисленные в Подразделе H.5.3, необязательные.
H.5.2.1. Активный режим
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; -- здесь ошибка — в таблице t1 отсутствует столбец "c"
END LOOP;
END;
$function$;
CREATE FUNCTION
postgres=# select f1(); -- ошибка не обнаружена, так как таблица 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; -- здесь ошибка — в таблице t1 отсутствует столбец "c"
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)H.5.2.1.1. Триггеры
Чтобы проверить триггер, необходимо указать отношение, которое будет использоваться вместе с триггерной функцией:
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');H.5.2.1.2. Параметры в комментариях
Расширение plpgsql_check позволяет заключать постоянные настройки в комментарии. Таким образом эти параметры исключаются из проверки исходного кода функции. Синтаксис следующий:
@plpgsql_check_option: имя_параметра [=] значение [, имя_параметра [=] значение ...]
Настройки из параметров в комментариях имеют наивысший приоритет, но комментирование можно отключить, задав для параметра 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;
H.5.2.1.3. Полная проверка кода
Функцию plpgsql_check_function можно использовать для массовой проверки функций, процедур и триггеров. Попробуйте следующие запросы:
-- проверка всех нетриггерных функций plpgsql 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;
или
-- проверка всех триггерных функций plpgsql
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, oldtable=>t.tgoldtable, newtable=>t.tgnewtable) cf
WHERE n.nspname = 'public' and l.lanname = 'plpgsql';или
-- проверка всех функций plpgsql (триггерных и нетриггерных)
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),
oldtable=>pg_trigger.tgoldtable,
newtable=>pg_trigger.tgnewtable) 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;H.5.2.2. Пассивный режим
Этот режим рекомендуется использовать только на этапе разработки или подготовки к выпуску.
Код функций может проверяться при выполнении. Для этого расширение plpgsql_check необходимо загрузить через postgresql.conf.
Для настройки пассивного режима выберите значения для параметров конфигурации plpgsql_check.mode и plpgsql_check.fatal_errors и задайте параметрам plpgsql_check.show_nonperformance_warnings и plpgsql_check.show_performance_warnings значение false.
Синтаксис для включения пассивного режима:
load 'plpgsql'; -- для версий 1.1 и выше это не требуется load 'plpgsql_check'; set plpgsql_check.mode = 'every_start'; -- сканирование всего кода перед его выполнением SELECT fx(10); -- запуск функций - функция проверяется перед запуском
H.5.2.3. Предупреждения о совместимости
H.5.2.3.1. Назначение строки переменной refcursor
Переменные Postgres Pro типа cursor и refcursor — это строковые переменные с расширенными возможностями, которые содержат уникальное имя соответствующего портала (внутренней структуры PostgreSQL для реализации курсора). До версии PostgreSQL 16 имя портала совпадало с именем курсорной переменной. В версиях PostgreSQL 16 и выше механизм изменился. По умолчанию соответствующему порталу начало присваиваться уникальное имя. Это позволило решить некоторые проблемы с курсорами во вложенных блоках или при их использовании в рекурсивно вызываемой функции.
При упомянутых изменениях переменная типа refcursor принимает значение другой переменной типа refcursor или другой курсорной переменной (если курсор открыт).
-- устаревший шаблон DECLARE cur CURSOR FOR SELECT 1; rcur refcursor; BEGIN rcur := 'cur'; OPEN cur; ... -- новый шаблон DECLARE cur CURSOR FOR SELECT 1; rcur refcursor; BEGIN OPEN cur; rcur := cur; ...
Когда флаг функции plpgsql_check_function compatibility_warnings активен, plpgsql_check проверяет, правильно ли назначается и возвращается значение переменной 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)
H.5.2.4. Ограничения
Расширение 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 в пассивном режиме приводит к небольшим издержкам, поэтому этот режим рекомендуется использовать только на стадии разработки или подготовки к выпуску.
H.5.2.4.1. Динамические SQL-запросы
Расширение не проверяет запросы, которые собираются в ходе выполнения. Определить результаты динамических запросов невозможно, поэтому plpgsql_check не может задать корректный тип для переменных типа запись и не может поверить зависимые SQL-операторы и выражения.
Когда тип переменных записи неизвестен, его можно явно задать, используя указание pragma typetype:
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-инъекций. Его нельзя использовать для проверки безопасности. Некоторые проблемы не будут выявлены в ходе проверки. Также могут возникнуть ложные оповещения, вероятно, когда переменная проверяется другой командой или когда тип значения составной.
H.5.2.4.2. Переменные типа refcursor
Расширение plpgsql_check нельзя использовать для обнаружения структуры курсоров, на которые даётся ссылка. Ссылка на курсор на языке PL/pgSQL реализована как имя глобального курсора. Во время проверки в большинстве случаев это имя неизвестно и глобальный курсор не существует. Для статического анализа это представляет серьёзную проблему. Невозможно определить, какой тип необходимо установить для переменных типа запись, а также проверить зависимые SQL-операторы и выражения. Решение проблемы такое же, как и для динамических SQL-запросов. С типом refcursor не используйте переменную типа запись в качестве целевой или отключите plpgsql_check для таких функций.
CREATE OR REPLACE FUNCTION foo(refcur_var refcursor) RETURNS void AS $$ DECLARE rec_var record; BEGIN FETCH refcur_var INTO rec_var; -- здесь работа plpgsql_check останавливается RAISE NOTICE '%', rec_var; -- ошибка из-за того, что запись rec_var ещё не назначена
В этом случае не следует использовать тип record (используйте rowtype):
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;
H.5.2.4.3. Временные таблицы
Расширение plpgsql_check не может проверять запросы к временным таблицам, которые создаются в процессе выполнения функции PL/pgSQL. В этом случае необходимо заранее создать временную таблицу специально для проверки или отключить 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, что делает возможным статическую проверку. Ещё один вариант — использовать обёртку сторонних данных для шаблонов.
Можно использовать указание pragma 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)');
...H.5.2.5. Список зависимостей
Функция 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, anyenumtype, anyrangetype, anycompatibletype и anycompatiblerangetype.
H.5.2.6. Профилировщик
В расширение plpgsql_check входит простой профилировщик функций и процедур PL/pgSQL. Он может работать с доступом или без доступа к разделяемой памяти, что определяется параметром конфигурации shared_preload_libraries. Когда plpgsql_check инициализируется shared_preload_libraries, выделяется разделяемая память, в которой сохраняются профили функции. Когда выделить разделяемую память не удаётся, профиль сохраняется в памяти сеанса.
Из-за зависимостей параметр shared_preload_libraries должен содержать plpgsql первым.
postgres=# show shared_preload_libraries ; ┌──────────────────────────┐ │ shared_preload_libraries │ ╞══════════════════════════╡ │ plpgsql,plpgsql_check │ └──────────────────────────┘ (1 row)
Профилировщик активен, если параметр конфигурации plpgsql_check.profiler включён. Профилировщику не требуется разделяемая память, но если её недостаточно, он будет ограничен только активным сеансом. Чтобы активировать профилировщик, необходимо вызвать функцию plpgsql_check_profiler с аргументом true, чтобы отключить — с аргументом false (можно также использовать буквенные значения on и off).
Инициализировать plpgsql_check необходимо до выполнения любой функции PL/pgSQL. Только в этом случае гарантируется корректная работа профилировщика и трассировщика. Вместо shared_preloaded_libraries можно использовать команду load 'plpgsql_check'.
Когда инициализация plpgsql_check происходит через shared_preload_libraries, можно использовать другой параметр конфигурации для определения объёма разделяемой памяти, используемой профилировщиком: plpgsql_check.profiler_max_shared_chunks.
Профилировщик также извлекает идентификатор запроса для каждой инструкции, которая содержит выражение или оптимизируемый оператор. Обратите внимание, что для этого требуется установить модуль pg_stat_statements или другое аналогичное стороннее расширение. Извлечение идентификатора запроса возможно с некоторыми ограничениями:
Если выражение PL/pgSQL содержит в себе операторы, будет извлечён только идентификатор запроса верхнего уровня.
Профилировщик не вычисляет идентификатор самостоятельно, а опирается на данные внешних расширений и модулей, например pg_stat_statements. Это означает, что в зависимости от поведения внешнего расширения или модуля идентификатор для некоторых операторов могут не отображаться. Например, pg_stat_statements не раскрывает идентификаторы запросов с DDL-операторами.
Идентификатор запроса извлекается только для команд, содержащих выражения. Это означает, что функция
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).
H.5.2.6.1. Метрики покрытия
plpgsql_check предоставляет две функции:
plpgsql_coverage_statements(имя)plpgsql_coverage_branches(имя)
H.5.2.6.2. Сторонний профилировщик
Существует ещё один хороший профилировщик PL/pgSQL — https://github.com/glynastill/plprofiler.
Расширение plpgsql_check просто в использовании и практично.
Профилировщик plprofiler более сложный. В результате его работы строятся графики вызова, на основе которых он может создать flame-график времени выполнения.
Оба профилировщика можно использовать вместе с функциями отслеживания — встроенной функциональностью Postgres Pro.
set track_functions to 'pl'; ... select * from pg_stat_user_functions;
H.5.2.7. Трассировщик
Расширение plpgsql_check предоставляет возможности для отслеживания. Используя параметр конфигурации plpgsql_check.tracer_verbosity, можно определить уровень детализации трассировки.
postgres=# do $$ begin perform fx(10,null, 'now', e'stehule'); 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" => 'stehule' 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(true), и отключить с помощью этой же функции с аргументом false (можно также использовать буквенные значения on и off).
Предупреждение
Трассировка негативно влияет на производительность (в отличие от профилирования).
С помощью параметра конфигурации plpgsql_check.tracer_errlevel можно настроить уровень детализации вывода трассировщика. Длина вывода ограничена значением, указанным в параметре конфигурации plpgsql_check.tracer_variable_max_length.
Сначала трассировщик должен быть явно активирован суперпользователем. Для этого для параметра plpgsql-check.enable-tracer необходимо задать значение on через командную строку или в файле postgresql.conf. Это гарантирует соблюдение требований безопасности. Трассировщик отображает содержание переменных PL/pgSQL, поэтому конфиденциальная информация может оказаться доступной непривилегированным пользователям (когда они запускают функцию, определяющую контекст безопасности). Затем необходимо загрузить расширение plpgsql_check. Это можно сделать, запустив выполнение некоторых функций plpgsql_check или явно с помощью команды load 'plpgsql_check';. Можно использовать следующие параметры конфигурации: shared_preload_libraries, local_preload_libraries или session_preload_libraries.
Внимание
Трассировщик выводит содержимое переменных или аргументов функций. Содержимое функций, определяющих контекст безопасности, может включать конфиденциальную информацию. По этой причине по умолчанию трассировка отключена, а включить её может только суперпользователь через параметр plpgsql-check.enable-tracer.
В режиме детализации terse вывод сокращается:
postgres=# set plpgsql_check.tracer_verbosity TO terse; SET postgres=# do $$ begin perform fx(10,null, 'now', e'stehule'); 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
В режиме детализации verbose вывод расширяется, включая подробную информацию об операторах:
postgres=# do $$ begin perform fx(10,null, 'now', e'stehule'); 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" => 'stehule' 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)
Особенность трассировщика — отслеживание оператора ASSERT, если для параметра plpgsql_check.trace_assert задано значение on. Уровень детализации определяется параметром конфигурации plpgsql_check.trace_assert_verbosity. Эта функциональность не зависит от значения plpgsql.check_asserts. Её можно использовать, даже если проверка утверждений на языке PL/pgSQL отключена.
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'stehule'); 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" => 'stehule' 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'stehule')" 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'stehule'); 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" => 'stehule' NOTICE: #0 PL/pgSQL function inline_code_block line 1 at PERFORM DO
Трассировщик может отобразить информацию об использовании идентификатора буфера с данными о подтранзакциях (nxids). Выводимое значение tnl — число, соответствующее уровню вложенности транзакции (для PL/pgSQL оно зависит от глубины блоков обработчиков исключения).
H.5.2.7.1. Обнаружение незакрытых курсоров
Курсоры PL/pgSQL — это просто имена курсоров SQL. Жизненный цикл курсоров SQL не связан с областью действия соответствующей курсорной переменной PL/pgSQL. Курсоры SQL закрываются автоматически по окончании транзакции, однако, если транзакция длинная и открытых курсоров очень много, их закрытие может занять слишком много времени. Когда курсор больше не требуется, рекомендуется явно закрыть его с помощью оператора CLOSE. В противном случае возможны серьёзные проблемы с памятью.
Когда оператор OPEN пытается использовать курсор, который ещё не закрыт, выводится предупреждение. Предупреждение можно отключить, задав для параметра plpgsql_check.cursors_leaks значение off. Проверка не выполняется, если подпрограмма вызывается рекурсивно.
Незакрытые курсоры можно проверить сразу после завершения функции. По умолчанию проверка отключена. Её необходимо включить, задав для параметра plpgsql_check.strict_cursors_leaks значение on.
Информация о незакрытом курсоре выводится один раз.
H.5.2.7.2. Использование с plugin_debugger
Если расширение plpgsql_check используется вместе с plugin_debugger (инструмент для отладки PL/pgSQL), инициализация расширения plpgsql_check должна выполняться после plugin_debugger (поскольку plugin_debugger не поддерживает совместного использования API для отладки PL/pgSQL). Пример настройки (через postgresql.conf):
shared_preload_libraries = 'plugin_debugger,plpgsql,plpgsql_check'
H.5.2.8. Функции pragma
Можно настроить поведение расширения plpgsql_check в рамках проверяемой функции, добавив функцию «pragma». Это альтернатива указаниям PRAGMA на языках PL/SQL и ADA. Язык PL/pgSQL не поддерживает указания PRAGMA, но расширение plpgsql_check распознаёт функцию с именем plpgsql_check_pragma и принимает параметры, задаваемые этой функцией. Эти параметры plpgsql_check действуют до окончания выполнения этой группы операторов.
CREATE OR REPLACE FUNCTION test()
RETURNS void AS $$
BEGIN
...
-- отключение проверки для следующих операторов
PERFORM plpgsql_check_pragma('disable:check');m
...
-- включение проверки
PERFORM plpgsql_check_pragma('enable:check');
...
END;
$$ LANGUAGE plpgsql;plpgsql_check_pragma — это постоянная функция, которая возвращает единицу. Она определяется расширением plpgsql_check. Ещё один способ задать функцию plpgsql_check_pragma представлен ниже:
CREATE OR REPLACE FUNCTION plpgsql_check_pragma(VARIADIC args[]) RETURNS int AS $$ SELECT 1 $$ LANGUAGE sql IMMUTABLE;
Функция с pragma в операторе DECLARE блока верхнего уровня также задаёт параметры на уровне функции.
CREATE OR REPLACE FUNCTION test()
RETURNS void AS $$
DECLARE
aux int := plpgsql_check_pragma('disable:extra_warnings');
...Также поддерживается более короткий синтаксис для pragma:
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)'; ...
H.5.2.8.1. Поддерживаемые указания pragma
echo:str— вывести строку (для тестирования). В строке могут быть «переменные»: @@id, @@name, @@signature.status:check,status:tracer,status:other_warnings,status:performance_warnings,status:extra_warnings,status:security_warningsВыводит текущую настройку (например, other_warnings enabled).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необходимо добавить pragma.type:varname typenameилиtype:varname (fieldname type, ...)— задать тип переменной записи.table: name (column_name type, ...)илиtable: name (like tablename)— создать эфемерную временную таблицу (при указании схемы доступна только схемаpg_temp).sequence: name— создать эфемерную временную последовательность.assert-schema: varname— check-time assertation — проверить, что схема, указанная в переменной, действительна.assert-table: [ varname_schema, ] , varname— проверить, что имя таблицы, указанное в переменных (путём трассировки констант), действительно.assert-column: [varname_schema, ], varname_table , varname— проверить, что столбец, указанный в переменных, действителен.
H.5.3. Аргументы функций
В списке ниже описаны аргументы некоторых функций plpgsql_check, в частности plpgsql_check_function:
-
funcoidoid Имя или сигнатура функции. Функциям требуется спецификация. В Postgres Pro любую функцию можно определить с помощью OID, имени или сигнатуры. Если OID или полная сигнатура функции известны, можно использовать параметр типа
regprocedure, например'fx()'::regprocedureили16799::regprocedure. Если имя функции уникально, например'fx', можно использовать только имя. Если имя неуникальное или функция не существует, возникнет ошибка.relidoidDEFAULT 0OID отношения, используемого с триггерной функцией. Оно необходимо для проверки любой триггерной функции. Указывает на таблицу, в которой будет работать триггер.
fatal_errorsbooleanDEFAULT trueВыводить только первую ошибку (чтобы избежать массивных списков ошибок).
other_warningsbooleanDEFAULT trueВыводить предупреждения, например при разном количестве аттрибутов в правой и левой части оператора присваивания, пересечении переменной и параметра функции, неиспользуемых переменных, нежелательном приведении типов и т. д.
extra_warningsbooleanDEFAULT trueВыводить предупреждения, например если пропущена команда
RETURN, если есть скрытые переменные, части кода, которые не используются, неиспользуемый параметр функции, неизменяемые переменные, изменяемые автоматические переменные и т. д.performance_warningsbooleanDEFAULT falseПредупреждения, связанные с производительностью, такие как объявленный тип с модификатором типа, приведение типов, неявные приведения в предложении
WHERE(что может приводить к неиспользованию индекса) и т. д.security_warningsbooleanDEFAULT falseПроверки безопасности, например на уязвимость SQL-инъекций.
compatibility_warningsbooleanDEFAULT falseПроверки, связанные с совместимостью, например на явное указание внутренних имён курсоров в переменных типа refcursor и cursor, что считается устаревшим.
anyelementtyperegtypeDEFAULT 'int'Фактический тип, который будет использоваться при проверке типа
anyelement.anyenumtyperegtypeDEFAULT '-'Фактический тип, который будет использоваться при проверке типа
anyenum.anyrangetyperegtypeDEFAULT 'int4range'Фактический тип, который будет использоваться при проверке типа
anyrange.anycompatibletypeDEFAULT 'int'Фактический тип, который будет использоваться при проверке типа
anycompatible.anycompatiblerangetypeDEFAULT 'int4range'Фактический тип, который будет использоваться при проверке диапазонного типа
anycompatible.without_warningsbooleanDEFAULT falseОтключить все предупреждения (все параметры xxxx_warning игнорируются, быстрое переопределение).
all_warningsbooleanDEFAULT falseВключить все предупреждения (другие параметры xxx_warning игнорируются; используется, чтобы разом включить все предупреждения).
newtabletextDEFAULT NULLИмя переходной таблицы NEW. Этот параметр является обязательным, если переходные таблицы используются в триггерных функциях.
oldtabletextDEFAULT NULLИмя переходной таблицы OLD. Этот параметр является обязательным, если переходные таблицы используются в триггерных функциях.
use_incomment_optionsbooleanDEFAULT trueЕсли значение true, параметры в комментариях активны.
incomment_options_usage_warningbooleanDEFAULT falseЕсли значение true, выводится предупреждение, что некоторые параметры помещаются в комментарии.
constant_tracingbooleanDEFAULT trueЕсли значение true, переменная, имеющая постоянное содержимое, может использоваться как константа (это работает только в некоторых простых случаях, и содержимое переменной не должно быть двусмысленным).
H.5.4. Параметры конфигурации
Ниже приведён список параметров конфигурации plpgsql-check:
-
plpgsql_check.mode= disabled | by_function | fresh_start | every_start Режим работы plpgsql_check. Значение по умолчанию —
by_function(расширенная проверка только в активном режиме путём вызова функцииplpgsql_check_function).fresh_startозначает «холодный» запуск (сначала вызывается функция).-
plpgsql_check.fatal_errors= yes | no Проверка критических ошибок.
-
plpgsql_check.show_nonperformance_warnings= true | false Отображение предупреждений, не связанных с производительностью.
-
plpgsql_check.show_performance_warnings= true | false Отображение предупреждений, связанных с производительностью.
-
plpgsql_check.profiler= on | off Проверка активности профилировщика.
-
plpgsql_check.profiler_max_shared_chunks Определяет максимальное количество порций операторов, которые сохраняются в разделяемой памяти. Всё содержимое каждой функции (или процедуры) PL/pgSQL разбивается на порции по 30 операторов. При необходимости содержимое одной функции может храниться в нескольких порциях. Размер одной порции — 1704 байта. Значение по умолчанию для этого параметра — 15000 порций. Этого должно быть достаточно для больших проектов, содержащих сотни тысяч операторов на языке PL/pgSQL. Максимально потребуется 24 МБ памяти. Если для проекта не требуется столько порций, для параметра можно задать меньшее значение, чтобы сократить потребление памяти. Минимальное значение — 50 порций (потребуется 83 КБ памяти), максимальное значение — 100000 порций (потребуется 163 МБ памяти). Чтобы изменить этот параметр, требуется перезапустить Postgres Pro.
-
plpgsql_check.enable_tracer= on | off Делает возможным использование трассировки. Для настройки этой переменной требуются права суперпользователя.
-
plpgsql_check.tracer= on | off Включение активности трассировщика.
-
plpgsql_check.tracer_verbosity= terse | default | verbose Уровень детализации вывода трассировщика. Если для параметра заданы значения
terseилиdefault, будет отображаться начало или конец функций. Еслиverbose— начало или конец операторов. При уровне детализацииdefaultиverboseвыводится содержимое аргументов функций. Содержимое связанных переменных выводится при уровнеverbose.-
plpgsql_check.tracer_errlevel Уровень ошибок в выводе трассировщика. Значение по умолчанию:
notice.-
plpgsql_check.tracer_variable_max_length Ограничение вывода трассировщика.
-
plpgsql_check.trace_assert= on | off Трассировка оператора
ASSERT.-
plpgsql_check.trace_assert_verbosity= default | verbose Уровень детализации вывода при трассировке оператора
ASSERT. Если для параметра задано значениеdefault, выводятся все переменные функции или процедуры при значении выражения assert false. Если для параметра задано значениеverbose, выводятся все переменные из всех блоков PL/pgSQL.-
plpgsql_check.cursors_leaks= on | off Выводить предупреждение, когда оператор
OPENпытается использовать ещё не закрытый курсор. Значение по умолчанию:on.-
plpgsql_check.strict_cursors_leaks= on | off Проверять незакрытые курсоры сразу после завершения функции. Значение по умолчанию:
off.
H.5.5. Автор
Павел Стехуле (Pavel Stehule) <pavel.stehule@gmail.com>