41.11. PL/pgSQL изнутри #
В этом разделе обсуждаются некоторые детали реализации, которые пользователям PL/pgSQL важно знать.
41.11.1. Подстановка переменных #
SQL-операторы и выражения внутри функции на PL/pgSQL могут ссылаться на переменные и параметры этой функции. За кулисами PL/pgSQL заменяет параметры запросов для таких ссылок. Параметры будут заменены только в местах, где они синтаксически допустимы. Как крайний случай, рассмотрим следующий пример плохого стиля программирования:
INSERT INTO foo (foo) VALUES (foo(foo));
Первый раз foo
появляется на том месте, где синтаксически должно быть имя таблицы, поэтому замены не будет, даже если функция имеет переменную foo
. Второй раз foo
встречается там, где должно быть имя столбца таблицы, поэтому замены не будет и здесь. Третий раз — на месте, где должно быть имя функции, поэтому замены не будет. Только последнее вхождение foo
является кандидатом на то, чтобы быть ссылкой на переменную функции PL/pgSQL.
Объяснить принцип действия можно ещё и так: при подстановке переменных в SQL-команду могут вставляться только значения данных; при этом нельзя динамически менять объекты базы данных, на которые ссылается команда. (Для этого нужно сконструировать строку команды динамически, как описано в Подразделе 41.5.4.)
Если имена переменных синтаксически не отличаются от названий столбцов таблицы, то возможна двусмысленность и в ссылках на таблицы. Является ли данное имя ссылкой на столбец таблицы или ссылкой на переменную? Изменим предыдущий пример:
INSERT INTO dest (col) SELECT foo + bar FROM src;
Здесь dest
и src
должны быть именами таблиц, col
должен быть столбцом dest
. Однако foo
и bar
могут быть как переменными функции, так и столбцами src
.
По умолчанию, PL/pgSQL выдаст ошибку, если имя в операторе SQL может относиться как к переменной, так и к столбцу таблицы. Ситуацию можно исправить переименованием переменной, переименованием столбца, точной квалификацией неоднозначной ссылки или указанием PL/pgSQL машине, какую интерпретацию предпочесть.
Самое простое решение — переименовать переменную или столбец. Общее правило кодирования предполагает использование различных соглашений о наименовании для переменных PL/pgSQL и столбцов таблиц. Например, если имена переменных всегда имеют вид v_
, а имена столбцов никогда не начинаются на имя
v_
, то конфликты исключены.
В качестве альтернативы можно дополнить имена неоднозначных ссылок, чтобы сделать их точными. В приведённом выше примере src.foo
однозначно бы определялась, как ссылка на столбец таблицы. Чтобы сделать однозначной ссылку на переменную, переменная должна быть объявлена в блоке с меткой, и далее нужно использовать эту метку (см. Раздел 41.2). Например:
<<block>> DECLARE foo int; BEGIN foo := ...; INSERT INTO dest (col) SELECT block.foo + bar FROM src;
Здесь block.foo
ссылается на переменную, даже если в таблице src
есть столбец foo
. Параметры функции, а также специальные переменные, такие как FOUND
, могут быть дополнены именем функции, потому что они неявно объявлены во внешнем блоке, метка которого совпадает с именем функции.
Иногда может быть не очень практичным исправлять таким способом все неоднозначные ссылки в большом куске PL/pgSQL кода. В таких случаях можно указать, чтобы PL/pgSQL разрешал неоднозначные ссылки в пользу переменных (это совместимо с PL/pgSQL до версии PostgreSQL 9.0), или в пользу столбцов таблицы (совместимо с некоторыми другими системами, такими как Oracle).
На уровне всей системы поведение PL/pgSQL регулируется установкой конфигурационного параметра plpgsql.variable_conflict
, имеющего значения: error
, use_variable
или use_column
(error
устанавливается по умолчанию при установке системы). Изменение этого параметра влияет на все последующие компиляции операторов в функциях на PL/pgSQL, но не на операторы уже скомпилированные в текущем сеансе. Так как изменение этого параметра может привести к неожиданным изменениям в поведении функций на 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
, как показано в Подразделе 41.5.4.
Замена переменных в настоящее время работает только в командах SELECT
, INSERT
, UPDATE
, DELETE
и командах, содержащих одну из вышеперечисленных (например, EXPLAIN
и CREATE TABLE ... AS SELECT
), потому что основной исполнитель SQL допускает использование параметров запроса только в этих командах. Чтобы использовать изменяемые имена или значения в других типах операторов (обычно называемые служебными), необходимо составить текст команды в виде строки и выполнить её в EXECUTE
.
41.11.2. Кеширование плана #
Интерпретатор PL/pgSQL анализирует исходный текст функции и строит внутреннее бинарное дерево инструкций при первом вызове функции (для каждого сеанса). В дерево инструкций полностью переводится вся структура операторов PL/pgSQL, но для выражений и команд SQL, используемых в функции, это происходит не сразу.
При первом выполнении в функции каждого выражения или команды SQL интерпретатор PL/pgSQL разбирает и анализирует команду для создания подготовленного к выполнению оператора с помощью функции SPI_prepare
менеджера интерфейса программирования сервера. Последующие обращения к этому выражению или команде повторно используют подготовленный к выполнению оператор. Таким образом, SQL-команды, находящиеся в редко посещаемой ветке кода условного оператора, не несут накладных расходов на разбор команд, если они так и не будут выполнены в текущем сеансе. Здесь есть недостаток, заключающийся в том, что ошибки в определённом выражении или команде не могут быть обнаружены, пока выполнение не дойдёт до этой части функции. (Тривиальные синтаксические ошибки обнаружатся в ходе первоначального разбора, но ничего более серьёзного не будет обнаружено до исполнения.)
Кроме того, PL/pgSQL (точнее, менеджер интерфейса программирования сервера) будет пытаться кешировать план выполнения для любого подготовленного к исполнению оператора. При каждом вызове оператора, если не используется план из кеша, генерируется новый план выполнения, и текущие значения параметров (то есть значения переменных PL/pgSQL) могут быть использованы для оптимизации нового плана. Если оператор не имеет параметров или выполняется много раз, менеджер интерфейса программирования сервера рассмотрит вопрос о создании и кешировании (для повторного использования) общего плана, не зависящего от значений параметров. Как правило, это происходит в тех случаях, когда план выполнения не очень чувствителен к имеющимся ссылкам на значения переменных PL/pgSQL. В противном случае выгоднее каждый раз формировать новый план. Более подробно поведение подготовленных операторов рассматривается в PREPARE.
Чтобы PL/pgSQL мог сохранять подготовленные операторы и планы выполнения, команды SQL в коде PL/pgSQL, должны использовать одни и те же таблицы и столбцы при каждом исполнении. А это значит, что в SQL-командах нельзя использовать названия таблиц и столбцов в качестве параметров. Чтобы обойти это ограничение, нужно сконструировать динамическую команду для оператора PL/pgSQL EXECUTE
— ценой будет разбор и построение нового плана выполнения при каждом вызове.
Изменчивая природа переменных типа record
представляет ещё одну проблему в этой связи. Когда поля переменной типа record
используются в выражениях или операторах, типы данных полей не должны меняться от одного вызова функции к другому, так как при анализе каждого выражения будет использоваться тот тип данных, который присутствовал при первом вызове. При необходимости можно использовать 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
, при анализе INSERT
, основной анализатор PostgreSQL знает, что строку 'now'
следует толковать как timestamp
, потому что целевой столбец таблицы logtable
имеет такой тип данных. Таким образом, 'now'
будет преобразовано в константу timestamp
при анализе INSERT
, а затем эта константа будет использоваться в последующих вызовах logfunc1
в течение всего сеанса. Разумеется, это не то, что хотел программист. Лучше было бы использовать функцию now()
или current_timestamp
.
В случае logfunc2
, основной анализатор PostgreSQL не знает, какого типа будет 'now'
и поэтому возвращает значение типа text
, содержащее строку now
. При последующем присваивании локальной переменной curtime
интерпретатор PL/pgSQL приводит эту строку к типу timestamp
, вызывая функции textout
и timestamp_in
. Таким образом, метка времени будет обновляться при каждом выполнении, как и ожидается программистом. И хотя всё работает как ожидалось, это не слишком эффективно, поэтому использование функции now()
по-прежнему значительно лучше.