Процедурные языки PostgreSQL

Процедурные языки PostgreSQL — это расширения системы управления базами данных PostgreSQL, позволяющие создавать пользовательские функции, хранимые процедуры и триггеры на языках, отличных от стандартного SQL. Они предоставляют возможность реализовывать сложную бизнес-логику непосредственно на стороне сервера базы данных, выполнять итеративные вычисления, обрабатывать исключения и управлять транзакциями. В отличие от чистого SQL, который является декларативным языком, процедурные языки являются императивными, что даёт разработчику гибкость в описании алгоритмов.

История и предпосылки

Изначально PostgreSQL, как и многие другие реляционные СУБД, поддерживал только встроенный язык SQL для написания функций. Однако для выполнения сложных операций, таких как циклическая обработка строк, условные переходы и обработка ошибок, возможностей SQL было недостаточно. Разработчики PostgreSQL реализовали архитектуру, позволяющую подключать различные языки программирования в качестве модулей-расширений. Первым таким языком стал PL/pgSQL, созданный ещё в середине 1990-х годов. В дальнейшем сообщество разработало поддержку PL/Tcl, PL/Perl, PL/Python, а также появились возможности для использования компилируемых языков, таких как C и Java.

Классификация процедурных языков

Все процедурные языки PostgreSQL делятся на две основные категории: доверенные (trusted) и недоверенные (untrusted). Доверенные языки работают в изолированной среде и не имеют доступа к файловой системе сервера, сетевым ресурсам или другим системным вызовам. Это обеспечивает безопасность, так как пользователь, имеющий право создавать функции на доверенном языке, не может нанести вред операционной системе. Недоверенные языки, напротив, предоставляют полный доступ к системным ресурсам, что позволяет выполнять, например, чтение и запись файлов или отправку HTTP-запросов. Создание функций на недоверенных языках обычно разрешено только суперпользователю базы данных.

Доверенные языки

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

PL/pgSQL — основной и наиболее распространённый процедурный язык. Он синтаксически близок к языку Oracle PL/SQL и предназначен для выполнения операций с данными, управления потоком выполнения и обработки исключений. PL/pgSQL является доверенным языком по умолчанию.
PL/Tcl — язык на основе Tcl. Доверенная версия PL/Tcl (pltcl) предоставляет безопасный подмножество команд Tcl. Недоверенная версия (pltclu) даёт полный доступ к возможностям Tcl.
PL/Perl — язык на основе Perl. Доверенная версия (plperl) использует механизм «taint mode» (режим заражения) для повышения безопасности. Недоверенная версия (plperlu) позволяет выполнять произвольный код Perl.
PL/Python — язык на основе Python. Доверенная версия (plpython3u) фактически является недоверенной, так как Python не предоставляет встроенных средств для изоляции кода. Однако на практике plpython3u часто используется как доверенный, если администратор базы данных контролирует, какие функции создаются.

Недоверенные языки

Недоверенные языки позволяют выполнять код, который может взаимодействовать с операционной системой. К ним относятся:

PL/Python (plpython3u) — как указано выше, формально недоверенный.
PL/Perl (plperlu) — недоверенная версия Perl.
PL/Tcl (pltclu) — недоверенная версия Tcl.
PL/Java — язык на основе Java, позволяющий использовать всю мощь экосистемы Java. Является недоверенным, так как Java-код может выполнять системные вызовы.
PL/R — язык на основе R, предназначенный для статистических вычислений. Является недоверенным.
PL/Lua — язык на основе Lua, доступный как расширение. Обычно реализуется как недоверенный.

Устройство и механизм работы

Процедурные языки реализуются в виде динамически загружаемых библиотек (shared objects). При создании функции на определённом языке PostgreSQL вызывает обработчик этого языка, который компилирует или интерпретирует исходный код функции. Для каждого языка существует свой обработчик, который отвечает за:

Разбор и проверку синтаксиса кода функции.
Выполнение кода в контексте текущей транзакции.
Взаимодействие с ядром PostgreSQL через специальный интерфейс (SPI — Server Programming Interface).
Обработку ошибок и исключений.

При вызове функции PostgreSQL передаёт ей аргументы, а после выполнения получает результат. Обработчик языка может кэшировать скомпилированный код для ускорения повторных вызовов.

Основные процедурные языки

PL/pgSQL

PL/pgSQL является языком по умолчанию и наиболее часто используемым. Он предоставляет следующие возможности:

Объявление переменных с указанием типов данных, включая составные типы и записи.
Условные операторы: IF, CASE.
Циклы: LOOP, WHILE, FOR (по диапазону чисел, по результату запроса, по массиву).
Обработка исключений с помощью блока EXCEPTION.
Динамический SQL через команду EXECUTE.
Курсоры для итерации по результирующим наборам данных.

Пример функции на PL/pgSQL, возвращающей количество сотрудников в отделе:

``sql CREATE OR REPLACE FUNCTION get_employee_count(dept_id INT) RETURNS INT AS $$ DECLARE emp_count INT; BEGIN SELECT COUNT(*) INTO emp_count FROM employees WHERE department_id = dept_id; RETURN emp_count; END; $$ LANGUAGE plpgsql; ``

PL/Python

PL/Python (plpython3u) позволяет писать функции на Python. Он даёт доступ ко всем библиотекам Python, установленным на сервере, что делает его мощным инструментом для обработки данных, машинного обучения и веб-запросов. Однако, как недоверенный язык, он требует осторожного использования.

Пример функции на PL/Python, возвращающей квадратный корень числа:

``sql CREATE OR REPLACE FUNCTION sqrt_py(x FLOAT) RETURNS FLOAT AS $$ import math return math.sqrt(x) $$ LANGUAGE plpython3u; ``

PL/Perl

PL/Perl (plperl и plperlu) предоставляет доступ к возможностям Perl, включая мощные средства обработки текста и регулярных выражений. Доверенная версия (plperl) ограничена в системных вызовах.

PL/Tcl

PL/Tcl (pltcl и pltclu) является одним из старейших процедурных языков. Он прост в изучении и подходит для быстрого прототипирования.

PL/Java

PL/Java позволяет использовать Java для написания хранимых процедур. Это требует установки Java Runtime Environment (JRE) на сервере и настройки соответствующего расширения. PL/Java даёт доступ к широкому спектру библиотек Java, но также является недоверенным.

Применение

Процедурные языки PostgreSQL применяются в следующих сценариях:

Реализация бизнес-логики: перенос сложных проверок и расчётов на сторону сервера базы данных, что снижает нагрузку на клиентские приложения и уменьшает сетевой трафик.
Триггеры: автоматическое выполнение кода при вставке, обновлении или удалении записей в таблицах. Например, триггер на PL/pgSQL может автоматически проверять целостность данных или обновлять агрегированные значения.
Пакетная обработка данных: выполнение массовых операций, таких как пересчёт балансов, генерация отчётов или миграция данных.
Интеграция с внешними системами: с помощью недоверенных языков (PL/Python, PL/Perl) можно отправлять HTTP-запросы, читать файлы, взаимодействовать с очередями сообщений.
Статистические вычисления: PL/R используется для выполнения сложных статистических моделей непосредственно в базе данных.

Производительность и ограничения

Производительность функций на процедурных языках может быть ниже, чем на компилируемых языках (например, C), из-за накладных расходов на интерпретацию. Однако для большинства задач, связанных с обработкой данных, это не является критичным. PL/pgSQL, будучи интерпретируемым, оптимизирован для работы с SQL-запросами и часто показывает хорошую скорость.

Основные ограничения:

Безопасность: недоверенные языки требуют строгого контроля над тем, кто может создавать функции.
Сложность отладки: отладка хранимых процедур сложнее, чем отладка кода на стороне клиента.
Переносимость: код, написанный на PL/pgSQL, может быть легко перенесён в другие СУБД, поддерживающие PL/SQL (например, Oracle), но код на PL/Python или PL/Perl является специфичным для PostgreSQL.

Интересные факты

PostgreSQL поддерживает возможность написания обработчиков для собственных процедурных языков. Разработчик может создать новый язык, реализовав несколько функций на C.
PL/pgSQL изначально назывался «PL/pgSQL» как сокращение от «Procedural Language/PostgreSQL Structured Query Language».
В некоторых дистрибутивах PostgreSQL по умолчанию устанавливаются только PL/pgSQL и, возможно, PL/Tcl. Остальные языки требуют дополнительной установки пакетов (например, postgresql-plpython3-16).
Существуют экспериментальные реализации процедурных языков, таких как PL/V8 (JavaScript) и PL/Rust.

Источники

Документация PostgreSQL: глава «Процедурные языки».
Официальное руководство по PL/pgSQL.
Расширения PostgreSQL: PL/Python, PL/Perl, PL/Tcl, PL/Java.
Книга «PostgreSQL. Основы» (автор: Б. Шварц, П. Зайцев, В. Ткаченко).
Статья «Procedural Languages in PostgreSQL» на сайте PostgreSQL Wiki.

BFOmetr — база данных и аналитика по компаниям России.

На главную BFOmetr →