Почему функции PL/pgSQL могут иметь побочный эффект, а функции SQL - нет?

Документ PostgreSQL гласит:

Все тело функции SQL анализируется перед выполнением любой из них. В то время как функция SQL может содержать команды, которые изменяют системные каталоги (например, CREATE TABLE), эффекты таких команд не будут видны во время анализа разбора более поздних команд в функции. Так, например, CREATE TABLE foo (...); INSERT INTO foo VALUES(...); не будет работать должным образом, если он упакован в одну функцию SQL, так как foo еще не будет существовать, когда INSERT команда разбирается

В такой ситуации рекомендуется использовать PL/pgSQL вместо функции SQL.

  • Почему "Рекомендуется использовать PL/pgSQL вместо функции SQL в такой ситуации", когда функция PL/pgSQL или SQL содержит команды, которые изменяют системные каталоги, такие как CREATE TABLE foo (...); INSERT INTO foo VALUES(...);?

  • Msgstr "Все тело функции SQL анализируется перед выполнением любой из них". Разве это не верно для функции PL/pgSQL? Какие различия между функциями SQL и функциями PL/pgSQL с точки зрения разбора и выполнения команд в их теле?

Источник

2 ответа

Решение

Вы сами набросали ключевое предложение в руководстве:

Все тело функции SQL анализируется перед выполнением любой из них.

Также прочитайте о стадии парсера в руководстве.

Он состоит из двух основных частей: синтаксического анализатора и процесса преобразования. Цитирование руководства:

процесс преобразования принимает дерево, переданное анализатором, в качестве входных данных и выполняет семантическую интерпретацию, необходимую для понимания того, на какие таблицы, функции и операторы ссылается запрос.

Если функция SQL содержит эти команды:

CREATE TABLE foo (...);
INSERT INTO foo VALUES(...);

Оба оператора планируются практически одновременно (на основе одного и того же снимка системных каталогов). Следовательно INSERT не может видеть таблицу "foo", предположительно созданную с помощью предыдущего CREATE команда. Это создает одну из следующих проблем:

  1. Если в вашей таблице нет другой таблицы с именем "foo"search_patch (пока) Postgres жалуется при попытке создать функцию:

    ERROR:  relation "foo" does not exist

  2. Если другая таблица с именем "foo" уже существует в вашем search_patch (и вы не используете конфликтующие имена столбцов), Postgres будет планировать INSERT на основе этой ранее существующей таблицы. Обычно это приводит к ошибке во время выполнения, если какие-либо значения вызывают конфликты в (неправильной!) Таблице. Или, если повезет, он может даже записать в эту таблицу без сообщения об ошибке! Очень подлый баг.

Этого не может быть с функцией PL/pgSQL, потому что она обрабатывает команды SQL как подготовленные операторы, запланированные и выполняемые последовательно. Таким образом, каждый оператор может видеть объекты, созданные в предыдущих операторах.

Следовательно, операторы, которые никогда не посещаются, никогда даже не планируются - в отличие от функций SQL. И план выполнения для операторов может быть кэширован в том же сеансе - также в отличие от функций SQL. Подробности о кэшировании планов в функциях PL/pgSQL смотрите в руководстве здесь.
Каждый подход имеет преимущества для некоторых случаев использования. Дальнейшее чтение:

Источник

Функции Plpgsql анализируются и проверяются на синтаксис во время определения, а затем при первом выполнении генерируется план.

https://www.postgresql.org/docs/current/static/plpgsql-implementation.html

затем этот план выполняется с заданными параметрами.

Временные файлы работают должным образом, кроме тех, которые уже существуют при первом выполнении.

Как уже упоминалось, использование динамического SQL (EXECUTE) - это способ помешать планировщику, позволяющему получить доступ к произвольным таблицам.

https://www.postgresql.org/docs/current/static/plpgsql-statements.html

Источник
Другие вопросы по тегам