Расширяемость
Языки программирования
12
Авторские права
© Postgres Professional, 2020 год.
Авторы: Егор Рогов, Павел Лузанов
Использование материалов курса
Некоммерческое использование материалов курса (презентации,
демонстрации) разрешается без ограничений. Коммерческое
использование возможно только с письменного разрешения компании
Postgres Professional. Запрещается внесение изменений в материалы
курса.
Обратная связь
Отзывы, замечания и предложения направляйте по адресу:
Отказ от ответственности
Компания Postgres Professional не несет никакой ответственности за
любые повреждения и убытки, включая потерю дохода, нанесенные
прямым или непрямым, специальным или случайным использованием
материалов курса. Компания Postgres Professional не предоставляет
каких-либо гарантий на материалы курса. Материалы курса
предоставляются на основе принципа «как есть» и компания Postgres
Professional не обязана предоставлять сопровождение, поддержку,
обновления, расширения и изменения.
2
Темы
Языки серверного программирования
Доверенные и недоверенные языки
Подключение нового языка
Трансформации типов
Интерфейс SPI для работы с базой
Зачем нужны языки и из чего можно выбирать
3
Языки программирования
«Встроенные»
C
SQL
Стандартные (поддержка сообщества)
PL/pgSQL
PL/Perl, PL/Python, PL/Tcl
Сторонние
PL/Java, PL/V8, PL/R, PL/Lua, ...
Разумеется, к PostgreSQL можно обращаться «извне» из любых языков
программирования (ЯП), для которых реализована поддержка клиент-
серверного протокола. Но разные ЯП можно использовать и для
программирования на стороне сервера: для написания хранимых
функций и процедур, триггеров и т. п.
В PostgreSQL «встроены» два ЯП: C, на котором написана вся система,
и SQL. Эти языки доступны всегда.
В систему также входят четыре ЯП, которые имеются в стандартной
поставке и поддерживаются сообществом. С одним из них – PL/pgSQL –
мы уже хорошо знакомы. Он доступен по умолчанию и наиболее часто
используется на стороне сервера. Другие языки – PL/Perl, PL/Python и
PL/Tcl – надо устанавливать как расширения. Такой (несколько
странный) выбор языков обусловлен историческими причинами.
В документации языки, отличные от C и SQL, называются
процедурными, отсюда и приставка «PL» в названиях. Неудачное
название: никто не мешает подключить к PostgreSQL функциональный
или еще какой-нибудь язык. Поэтому лучше понимать PL как
programming language, а не как procedural language.
PostgreSQL – расширяемая система, поэтому имеется возможность
добавления и других ЯП.
4
Доверенные языки
Доверенные
гарантируют работу пользователя в рамках выданного доступа
ограничено взаимодействие с окружением и внутренностями СУБД,
язык должен позволять работать в «песочнице»
по умолчанию доступ для всех пользователей
Недоверенные
доступна полная функциональность языка
доступ только у суперпользователей
обычно к названию языка добавляют «u»: plperl → plperlu
Все ЯП можно разделить на доверенные (trusted) и недоверенные
(untrusted).
Доверенные языки должны соблюдать ограничения доступа,
установленные в системе. С помощью функций, написанных на таких
языках, пользователь не должен суметь получить доступ к чему-либо,
если такой доступ ему не предоставлен.
По сути это означает, что язык должен ограничивать взаимодействие
пользователя с окружением (например, с операционной системой) и
с внутренностями СУБД (чтобы нельзя было обойти обычные проверки
доступа). Не все реализации языков умеют работать в таком режиме
«песочницы».
Использование доверенного языка является безопасным, поэтому
доступ к нему получают все пользователи (для роли public выдается
привилегия usage на язык).
Недоверенные языки не имеют никаких ограничений. В частности,
функция на таком языке может выполнять любые операции в ОС
с правами пользователя, запустившего сервер базы данных. Это может
быть небезопасным, поэтому доступ к такому языку имеют только
суперпользователи PostgreSQL.
Напомним, что, если необходимо, суперпользователь может создать
функцию на недоверенном языке с указанием SECURITY DEFINER и
выдать право на ее исполнение обычным пользователям.
6
Подключение нового языка
validator
CREATE FUNCTION f(...)
AS $$ ... $$
LANGUAGE ...
SELECT f(...)
DO $$ ... $$
LANGUAGE ...
pg_proc
интер-
претатор
языка
handler
inline
статическая проверка
check_function_bodies
передача на выполнение,
обеспечение «песочницы»,
преобразование типов
однократное выполнение
Интерфейс для подключения нового ЯП для использования на стороне
сервера включает всего три функции.
При создании хранимой подпрограммы (функции или процедуры), ее
код проверяется на наличие ошибок (validator). Если проверка
проходит успешно, подпрограмма сохраняется в системном каталоге.
Как мы знаем, текст подпрограммы записывается в виде обычной
строки, и ровно эта строка и сохраняется. Таким образом, компиляция
не поддерживается (что не исключает использование JIT-компиляции).
Проверка может отключаться параметром check_function_bodies;
утилита pg_dump пользуется этим, чтобы уменьшить зависимость
от порядка создания объектов.
При вызове подпрограммы ее текст передается интерпретатору на
выполнение, а полученный ответ возвращается в систему (handler).
Если язык объявлен доверенным, интерпретатор должен запускаться
в «песочнице». Здесь также решается важная задача преобразования
типов. Не все типы SQL могут иметь аналоги в ЯП. Обычный подход
в этом случае – передача текстового представления типа, но
реализация может также учитывать определенные пользователем
трансформации типов.
При выполнении SQL-команды DO интерпретатору передается код для
однократного выполнения (inline).
Из этих трех функций только функция-handler является обязательной,
остальные могут отсутствовать.
8
Задачи
Обработка информации, хранимой в базе данных
подпрограмма всегда выполняется в контексте подключения к БД
PL/pgSQL интегрирован с SQL
для остальных — интерфейс серверного программирования (SPI)
Вычисления, не связанные с базой данных
возможности PL/pgSQL сильно ограничены
эффективность
удобство использования
наличие готовых библиотек
специализированные задачи
Задачи, для решения которых используются хранимые подпрограммы,
можно условно поделить на две группы.
В первую входит работа с информацией, которая содержится внутри
базы данных. Здесь любые хранимые подпрограммы выигрывают
у внешних (клиентских) программ, поскольку они находятся ближе
к данным: во-первых, не требуется установка соединения с сервером,
и во-вторых, не требуется пересылка лишнего по сети. PL/pgSQL очень
удобен для таких задач, поскольку тесно интегрирован с SQL.
Подпрограммы на других языках могут пользоваться специальным
интерфейсом SPI для работы с базой данных, в контексте подключения
к которой они работают.
Ко второй группе можно отнести любые вычисления, не связанные
с обращением к базе данных. Здесь возможности PL/pgSQL сильно
ограничены. Начать с того, что он вычислительно не эффективен:
любое выражение вычисляется с помощью запроса (хоть в PostgreSQL
версии 13 эта ситуация и улучшена).
Если бы были важны только эффективность и универсальность, можно
было бы использовать язык C. Но писать на нем прикладной код крайне
дорого и долго.
Кроме того, язык PL/pgSQL достаточно старомоден и не располагает
возможностями и библиотеками, которые есть в современных ЯП, и он
может быть в принципе не пригоден для решения целого ряда задач.
Поэтому использование других языков (отличных от SQL, PL/pgSQL и
C) для серверного программирования может быть вполне оправдано.
9
Интерфейс SPI
клиент
PostgreSQL
низкоуровневый API
разбор
планирование
выполнение
функция
на языке C
функция
на другом языке
SPI
движок
SQL
PostgreSQL располагает внутренним низкоуровневым API для работы
с базой данных. Сюда входят функции для разбора и переписывания
запроса, построения плана, выполнения запроса (включая обращения
к таблицам, индексам и т. п.). Это самый эффективный способ работы
с данными, но он требует внимания ко многим деталям, таким, как
управление памятью, установка блокировок и т. д.
Движок SQL, являющийся частью каждого обслуживающего процесса,
пользуется именно этим, низкоуровневым, API.
Для удобства имеется более высокоуровневый (но в ряде случаев
менее эффективный) интерфейс: SPI, Server Programming Interface.
Интерфейс SPI рассчитан на язык C: подпрограмма на C может
пользоваться как этим интерфейсом, так и – при необходимости –
низкоуровневым.
Но, как правило, авторы языков программирования предоставляют
обертки, позволяющие подпрограммам на этих языках тоже
пользоваться SPI. Это значительно выгоднее, чем инициировать из
подпрограммы новое соединение и использовать для работы с базой
возможность клиент-серверного протокола.
11
Из чего выбирать
Интенсивная работа с данными
SQL, PL/pgSQL
Проверенные, часто используемые
PL/Perl, PL/Python, PL/V8, PL/Java
Другие языки общего назначения
PL/Go, PL/Lua, ...
Специальные задачи
PL/R (статистика), PL/Proxy (удаленные вызовы), ...
Максимальная эффективность
C
Если хранимые процедуры используются для интенсивной работы
с данными, лучшим выбором скорее всего будут обычные SQL и
PL/pgSQL.
Для других задач из штатных языков часто используют PL/Perl и
PL/Python, а из сторонних — PL/V8 и PL/Java. (С PL/V8 могут быть
сложности из-за того, что Debian и Red Hat перестали поставлять это
расширение в виде пакета из-за сложностей со сборкой.)
Можно попробовать и другие языки, например, PL/Go или PL/Lua, если
в этом есть смысл. Обязательно обращайте внимание на состояние
проекта, активность сообщества, возможность получить поддержку.
Есть ряд специализированных языков. Например, для статистической
обработки данных пригодится PL/R, для удаленного вызова процедур и
шардинга может использоваться PL/Proxy.
В документации перечислено некоторое количество языков:
Больше информации о доступных языках есть на вики:
Но и это не полный список. Есть множество проектов, хотя многие из
них не продвигаются дальше первой версии.
Не следует сбрасывать со счетов и язык C, если нужна максимальная
эффективность. Безусловно, это сложнее, но в документации и
в исходном коде PostgreSQL есть множество примеров, на основе
которых можно писать собственные функции.
13
Итоги
PostgreSQL позволяет подключать любые языки
Богатый выбор языков программирования позволяет решать
любые задачи на стороне сервера
14
Практика
1. Функция empapi.run, написанная на языке Python, принимает
параметр типа jsonb. Добавьте трансформацию, чтобы
избежать преобразований в текстовый вид и обратно.
2. Отправляйте пользователю, совершившему покупку
в магазине, письмо-подтверждение с указанием суммы.
В виртуальной машине настроен локальный почтовый
сервер, пересылающий любую исходящую почту
в локальный ящик пользователя student. В PostgreSQL нет
встроенной функции для отправки писем, но ее можно
реализовать на каком-либо недоверенном языке.
Убедитесь, что письмо не может быть отправлено до того,
как транзакция покупки будет завершена.
2. Для отправки пользуйтесь уже готовой функцией public.sendmail
(посмотрите ее определение) или напишите свою.
Посылать письмо внутри транзакции покупки неправильно: транзакция
может быть оборвана по какой-либо причине, а письмо уже уйдет.
Воспользуйтесь механизмом фоновых заданий: в транзакции
добавляйте задание на отправку письма. В таком случае оно будет
отправлено, только если транзакция завершится успешно.
15
Практика
1. Языки Python и Perl имеют удобный тип данных —
ассоциативный массив, — который отсутствует в PL/pgSQL.
В Python это dict (словарь), в Perl — hash (хеш-таблица).
Напишите на одном из этих языков функцию, получающую
на вход текстовую строку и возвращающую таблицу из слов,
которые встречаются в этой строке, с указанием количества
вхождений.
Решается ли эта задача на языке SQL?
2. Напишите функцию, которая по имени файла определяет
и выводит его MIME-тип, аналогично команде shell
file --brief --mime-type
1. Например:
SELECT * FROM words_count('the best of the best');
word | cnt
------+-----
the | 2
of | 1
best | 2
2. Воспользуйтесь расширением plsh, установленным в виртуальной
машине курса. Оно предоставляет язык PL/sh для написания функций
на языке командой оболочки Unix.
Пример использования, приведенный автором (Питер Эйзентраут):
CREATE FUNCTION concat(text, text) RETURNS text AS $$
#!/bin/sh
echo "$1$2"
$$ LANGUAGE plsh;
Обратите внимание, что обращение к параметрам возможно только по
номеру.