PL/pgSQL под капотом
Эта страница переведена при помощи нейросети GigaChat.
Этот раздел обсуждает некоторые детали реализации, которые часто важны для пользователей PL/pgSQL, чтобы знать.
Замена переменных
Операторы SQL и выражения внутри функции PL/pgSQL могут ссылаться на переменные и параметры функции. За кулисами PL/pgSQL заменяет параметры запроса такими ссылками. Параметры запроса будут заменены только там, где они синтаксически допустимы. В качестве крайнего случая рассмотрим этот пример плохого стиля программирования:
INSERT INTO foo (foo) VALUES (foo(foo));
Первое появление foo должно быть синтаксическим именем таблицы, поэтому оно не будет заменено, даже если функция имеет переменную с именем foo. Второе появление должно быть названием столбца этой таблицы, так что она также не будет заменяться. Аналогично третье появление должно быть именем функции, поэтому она тоже не будет заменена. Только последнее появление является кандидатом на то, чтобы быть ссылкой на переменную функции PL/pgSQL.
Еще один способ понять это заключается в том, что подстановка переменных может вставлять только значения данных в команду SQL. Она не может динамически изменять объекты базы данных, на которые ссылается команда. Если требуется сделать это, то нужно динамически построить строку команды, как объясняется в разделе «Выполнение динамических команд».
Поскольку имена переменных синтаксически ничем не отличаются от имен столбцов таблицы, могут возникнуть неоднозначности в операторах, которые также ссылаются на таблицы: предназначено ли данное имя для ссылки на столбец таблицы или переменную? Давайте изменим предыдущий пример следующим образом:
INSERT INTO dest (col) SELECT foo + bar FROM src;
Здесь dest и src должны быть именами таблиц, а col должен быть столбцом dest, но foo и bar вполне могут быть либо переменными функции, либо столбцами src.
По умолчанию, PL/pgSQL сообщит об ошибке, если имя в операторе SQL может относиться к переменной или столбцу таблицы. Можно решить такую проблему, переименовав переменную или столбец, уточнив неоднозначную ссылку или указав PL/pgSQL, какую интерпретацию следует предпочесть.
Простейшее решение – переименовать переменную или столбец. Общее правило кодирования заключается в использовании другой схемы именования для переменных PL/pgSQL, чем используется для имен столбцов. Например, если переменные функций называется последовательно v_something , и ни одно из имен столбцов не начинается с v_ , конфликтов не будет.
В качестве альтернативы можно уточнить неоднозначные ссылки, чтобы они были понятны. В приведенном выше примере src.foo была бы однозначной ссылкой на столбец таблицы. Чтобы создать однозначную ссылку на переменную, объявите ее в помеченном блоке и используйте метку блока (см. раздел «Структура PL/pgSQL»). Например:
<<block>>
DECLARE
foo int;
BEGIN
foo := ...;
INSERT INTO dest (col) SELECT block.foo + bar FROM src;
Здесь block.foo означает переменную, даже если существует столбец foo в src. Параметры функции, а также специальные переменные, такие как FOUND, могут быть квалифицированы именем функции, потому что они неявно объявлены во внешнем блоке, помеченном именем функции.
Иногда бывает непрактично исправлять все неоднозначные ссылки в большом объеме кода PL/pgSQL. В таких случаях можно указать, что PL/pgSQL должен разрешать неоднозначные ссылки как переменная (что совместимо с поведением PL/pgSQL до PostgreSQL 9.0) или как столбец таблицы (что совместимо с некоторыми другими системами, такими как Oracle).
Чтобы изменить это поведение в масштабах всей системы, установите параметр конфигурации plpgsql.variable_conflict на одно из значений error, use_variable или use_column (где error является значением по умолчанию при установке системы). Этот параметр влияет на последующую компиляцию операторов в функциях PL/pgSQL, но не на операторы, уже скомпилированные в текущем сеансе. Поскольку изменение этого параметра может привести к неожиданным изменениям в поведении функций PL/pgSQL, его может изменить только суперпользователь.
Также можно установить поведение на основе каждой функции, вставив одну из этих специальных команд в начало текста функции:
#variable_conflict error
#variable_conflict use_variable
#variable_conflict use_column
Эти команды влияют только на функцию, в которой они написаны, и переопределяют настройку plpgsql.variable_conflict. Пример:
CREATE FUNCTION stamp_user(id int, comment text) RETURNS void AS $$
#variable_conflict use_variable
DECLARE
curtime timestamp := now();
BEGIN
UPDATE users SET last_modified = curtime, comment = comment
WHERE users.id = id;
END;
$$ LANGUAGE plpgsql;
В команде UPDATE, curtime, comment и id будут ссылаться на переменные функции и параметры независимо от того, имеет ли users столбцы с этими именами. Обратите внимание, что пришлось уточнить ссылку на users.id в предложении WHERE, чтобы она относилась к столбцу таблицы. Но не обязательно было уточнять ссылку на comment в качестве цели в списке UPDATE, потому что синтаксически это должно быть столбцом users. Можно было бы написать ту же функцию без зависимости от настройки variable_conflict следующим образом:
CREATE FUNCTION stamp_user(id int, comment text) RETURNS void AS $$
<<fn>>
DECLARE
curtime timestamp := now();
BEGIN
UPDATE users SET last_modified = fn.curtime, comment = stamp_user.comment
WHERE users.id = stamp_user.id;
END;
$$ LANGUAGE plpgsql;
Замена переменных не происходит в строке команды, переданной EXECUTE или одной из его вариантов. Если нужно вставить изменяющееся значение в такую команду, сделайте это при создании строки значения или используйте USING, как показано в разделе «Выполнение динамических команд».
Замена переменных в настоящее время работает только в SELECT, INSERT, UPDATE, DELETE, и командах, содержащих одну из этих команд (например, EXPLAIN и CREATE TABLE ... AS SELECT), потому что основной механизм SQL позволяет параметры запроса только в этих командах. Чтобы использовать непостоянное имя или значение в других типах операторов (называемых утилитными операторами), нужно построить оператор утилиты как строку и EXECUTE его.
Кеширование плана
Интерпретатор PL/pgSQL анализирует исходный текст функции и создает внутреннее двоичное дерево инструкций при первом вызове функции (в пределах каждого сеанса). Дерево инструкций полностью преобразует структуру оператора PL/pgSQL, но отдельные выражения SQL и команды SQL, используемые в функции, не переводятся сразу.
При каждом выполнении выражения и команды SQL в функции интерпретатор PL/pgSQL анализирует команду для создания подготовленного оператора с использованием функции SPI_prepare менеджера SPI. Последующие обращения к этому выражению или команде повторно используют подготовленный оператор. Таким образом, функция с условными кодовыми путями, которые редко посещаются, никогда не понесет накладных расходов на анализ тех команд, которые никогда не выполняются в текущем сеансе. Недостатком является то, что ошибки в конкретном выражении или команде не могут быть обнаружены до тех пор, пока эта часть функции не будет достигнута во время выполнения. (Тривиальные синтаксические ошибки будут обнаружены во время начального прохода разбора, но все более глубокое обнаружение произойдет только во время выполнения.)
PL/pgSQL (или, точнее, менеджер SPI) также может попытаться кешировать план выполнения, связанный с любым конкретным подготовленным оператором. Если кешированный план не используется, то новый план выполнения генерируется при каждом посещении оператора, и текущие значения параметров (то есть значения переменных PL/pgSQL) могут использоваться для оптимизации выбранного плана. Если оператор не имеет параметров или выполняется много раз, менеджер SPI рассмотрит возможность создания общего плана, который не зависит от конкретных значений параметров, и его кеширования для повторного использования. Обычно это происходит только в том случае, если план выполнения не очень чувствителен к значениям переменных PL/pgSQL, на которые он ссылается. Если это так, генерация плана каждый раз является чистой победой. См. PREPARE для получения дополнительной информации о поведении подготовленных операторов.
Поскольку PL/pgSQL сохраняет подготовленные операторы и иногда планы выполнения таким образом, SQL-команды, которые появляются непосредственно в функции PL/pgSQL, должны ссылаться на одни и те же таблицы и столбцы при каждом выполнении, то есть нельзя использовать параметр в качестве имени таблицы или столбца в SQL-операторе. Чтобы обойти это ограничение, можно создавать динамические команды с помощью оператора PL/pgSQL EXECUTE – ценой проведения нового анализа синтаксиса и построения нового плана выполнения при каждом выполнении.
Изменчивая природа переменных записей создает еще одну проблему в этом контексте. Когда поля переменной записи используются в выражениях или операторах, типы данных полей не должны изменяться от одного вызова функции к другому, поскольку каждое выражение будет анализироваться с использованием типа данных, присутствующего при первом достижении выражения. EXECUTE можно использовать для обхода этой проблемы при необходимости.
Если одна и та же функция используется в качестве триггера для более чем одной таблицы, PL/pgSQL подготавливает и кеширует операторы независимо для каждой такой таблицы - то есть существует кеш для каждой комбинации функции триггера и таблицы, а не только для каждой функции. Это устраняет некоторые проблемы с различными типами данных; например, функция триггера сможет успешно работать со столбцом с именем key, даже если он случайно имеет разные типы в разных таблицах.
Аналогично, функции с полиморфными типами аргументов имеют отдельный кеш операторов для каждой комбинации фактических типов аргументов, для которых они были вызваны, так что различия в типах данных не вызывают неожиданных сбоев.
Кеширование операторов иногда может иметь удивительные последствия для интерпретации чувствительных ко времени значений. Например, есть разница между тем, что делают эти две функции:
CREATE FUNCTION logfunc1(logtxt text) RETURNS void AS $$
BEGIN
INSERT INTO logtable VALUES (logtxt, 'now');
END;
$$ LANGUAGE plpgsql;
и:
CREATE FUNCTION logfunc2(logtxt text) RETURNS void AS $$
DECLARE
curtime timestamp;
BEGIN
curtime := 'now';
INSERT INTO logtable VALUES (logtxt, curtime);
END;
$$ LANGUAGE plpgsql;
В случае logfunc1, основной парсер PostgreSQL знает, что при анализе INSERT строку 'now' следует интерпретировать как timestamp, потому что целевая колонка logtable имеет этот тип. Таким образом, 'now' будет преобразован в константу timestamp при анализе INSERT, а затем использоваться во всех вызовах logfunc1 в течение всего сеанса. Само собой разумеется, это не то, чего хотел программист. Лучше использовать функцию now() или current_timestamp.
В случае logfunc2, основной парсер PostgreSQL не знает, каким типом должен стать 'now', и поэтому он возвращает значение данных типа text, содержащее строку now. В ходе последующего присвоения локальной переменной curtime, интерпретатор PL/pgSQL приводит эту строку к типу timestamp, вызывая функции textout и timestamp_in для преобразования. Таким образом, вычисленное время метки обновляется при каждом выполнении так, как ожидает программист. Несмотря на то, что это работает так, как ожидалось, это не очень эффективно, поэтому использование функции now() все равно было бы лучшей идеей.