Расширяемость

Языки программирования

Авторские права

Авторы: Егор Рогов, Павел Лузанов

Использование материалов курса

Некоммерческое использование материалов курса (презентации,

демонстрации) разрешается без ограничений. Коммерческое

использование возможно только с письменного разрешения компании

Postgres Professional. Запрещается внесение изменений в материалы

курса.

Обратная связь

Отзывы, замечания и предложения направляйте по адресу:

edu@postgrespro.ru

Отказ от ответственности

Компания Postgres Professional не несет никакой ответственности за

любые повреждения и убытки, включая потерю дохода, нанесенные

прямым или непрямым, специальным или случайным использованием

материалов курса. Компания Postgres Professional не предоставляет

каких-либо гарантий на материалы курса. Материалы курса

предоставляются на основе принципа «как есть» и компания Postgres

Professional не обязана предоставлять сопровождение, поддержку,

обновления, расширения и изменения.

Темы

Языки серверного программирования

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

Подключение нового языка

Трансформации типов

Интерфейс SPI для работы с базой

Зачем нужны языки и из чего можно выбирать

Языки программирования

«Встроенные»

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.

https://postgrespro.ru/docs/postgresql/12/xplang

PostgreSQL – расширяемая система, поэтому имеется возможность

добавления и других ЯП.

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

Доверенные

гарантируют работу пользователя в рамках выданного доступа

ограничено взаимодействие с окружением и внутренностями СУБД,

язык должен позволять работать в «песочнице»

по умолчанию доступ для всех пользователей

Недоверенные

доступна полная функциональность языка

доступ только у суперпользователей

обычно к названию языка добавляют «u»: plperl → plperlu

Все ЯП можно разделить на доверенные (trusted) и недоверенные

(untrusted).

Доверенные языки должны соблюдать ограничения доступа,

установленные в системе. С помощью функций, написанных на таких

языках, пользователь не должен суметь получить доступ к чему-либо,

если такой доступ ему не предоставлен.

По сути это означает, что язык должен ограничивать взаимодействие

пользователя с окружением (например, с операционной системой) и

с внутренностями СУБД (чтобы нельзя было обойти обычные проверки

доступа). Не все реализации языков умеют работать в таком режиме

«песочницы».

Использование доверенного языка является безопасным, поэтому

доступ к нему получают все пользователи (для роли public выдается

привилегия usage на язык).

Недоверенные языки не имеют никаких ограничений. В частности,

функция на таком языке может выполнять любые операции в ОС

с правами пользователя, запустившего сервер базы данных. Это может

быть небезопасным, поэтому доступ к такому языку имеют только

суперпользователи PostgreSQL.

Напомним, что, если необходимо, суперпользователь может создать

функцию на недоверенном языке с указанием SECURITY DEFINER и

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

Подключение нового языка

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 является обязательной,

остальные могут отсутствовать.

Задачи

Обработка информации, хранимой в базе данных

подпрограмма всегда выполняется в контексте подключения к БД

PL/pgSQL интегрирован с SQL

для остальных — интерфейс серверного программирования (SPI)

Вычисления, не связанные с базой данных

возможности PL/pgSQL сильно ограничены

эффективность

удобство использования

наличие готовых библиотек

специализированные задачи

Задачи, для решения которых используются хранимые подпрограммы,

можно условно поделить на две группы.

В первую входит работа с информацией, которая содержится внутри

базы данных. Здесь любые хранимые подпрограммы выигрывают

у внешних (клиентских) программ, поскольку они находятся ближе

к данным: во-первых, не требуется установка соединения с сервером,

и во-вторых, не требуется пересылка лишнего по сети. PL/pgSQL очень

удобен для таких задач, поскольку тесно интегрирован с SQL.

Подпрограммы на других языках могут пользоваться специальным

интерфейсом SPI для работы с базой данных, в контексте подключения

к которой они работают.

Ко второй группе можно отнести любые вычисления, не связанные

с обращением к базе данных. Здесь возможности PL/pgSQL сильно

ограничены. Начать с того, что он вычислительно не эффективен:

любое выражение вычисляется с помощью запроса (хоть в PostgreSQL

версии 13 эта ситуация и улучшена).

Если бы были важны только эффективность и универсальность, можно

было бы использовать язык C. Но писать на нем прикладной код крайне

дорого и долго.

Кроме того, язык PL/pgSQL достаточно старомоден и не располагает

возможностями и библиотеками, которые есть в современных ЯП, и он

может быть в принципе не пригоден для решения целого ряда задач.

Поэтому использование других языков (отличных от SQL, PL/pgSQL и

C) для серверного программирования может быть вполне оправдано.

Интерфейс SPI

клиент

PostgreSQL

низкоуровневый API

разбор

планирование

выполнение

функция

на языке C

функция

на другом языке

SPI

движок

SQL

PostgreSQL располагает внутренним низкоуровневым API для работы

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

запроса, построения плана, выполнения запроса (включая обращения

к таблицам, индексам и т. п.). Это самый эффективный способ работы

с данными, но он требует внимания ко многим деталям, таким, как

управление памятью, установка блокировок и т. д.

Движок SQL, являющийся частью каждого обслуживающего процесса,

пользуется именно этим, низкоуровневым, API.

Для удобства имеется более высокоуровневый (но в ряде случаев

менее эффективный) интерфейс: SPI, Server Programming Interface.

https://postgrespro.ru/docs/postgresql/12/spi

Интерфейс SPI рассчитан на язык C: подпрограмма на C может

пользоваться как этим интерфейсом, так и – при необходимости –

низкоуровневым.

Но, как правило, авторы языков программирования предоставляют

обертки, позволяющие подпрограммам на этих языках тоже

пользоваться SPI. Это значительно выгоднее, чем инициировать из

подпрограммы новое соединение и использовать для работы с базой

возможность клиент-серверного протокола.

Из чего выбирать

Интенсивная работа с данными

SQL, PL/pgSQL

Проверенные, часто используемые

PL/Perl, PL/Python, PL/V8, PL/Java

Другие языки общего назначения

PL/Go, PL/Lua, ...

Специальные задачи

PL/R (статистика), PL/Proxy (удаленные вызовы), ...

Максимальная эффективность

Если хранимые процедуры используются для интенсивной работы

с данными, лучшим выбором скорее всего будут обычные SQL и

PL/pgSQL.

Для других задач из штатных языков часто используют PL/Perl и

PL/Python, а из сторонних — PL/V8 и PL/Java. (С PL/V8 могут быть

сложности из-за того, что Debian и Red Hat перестали поставлять это

расширение в виде пакета из-за сложностей со сборкой.)

Можно попробовать и другие языки, например, PL/Go или PL/Lua, если

в этом есть смысл. Обязательно обращайте внимание на состояние

проекта, активность сообщества, возможность получить поддержку.

Есть ряд специализированных языков. Например, для статистической

обработки данных пригодится PL/R, для удаленного вызова процедур и

шардинга может использоваться PL/Proxy.

В документации перечислено некоторое количество языков:

https://postgrespro.ru/docs/postgresql/12/external-pl

Больше информации о доступных языках есть на вики:

https://wiki.postgresql.org/wiki/PL_Matrix

Но и это не полный список. Есть множество проектов, хотя многие из

них не продвигаются дальше первой версии.

Не следует сбрасывать со счетов и язык C, если нужна максимальная

эффективность. Безусловно, это сложнее, но в документации и

в исходном коде PostgreSQL есть множество примеров, на основе

которых можно писать собственные функции.

Итоги

PostgreSQL позволяет подключать любые языки

Богатый выбор языков программирования позволяет решать

любые задачи на стороне сервера

Практика

1. Отправляйте пользователю, совершившему покупку

в магазине, письмо-подтверждение с указанием суммы.

В виртуальной машине настроен локальный почтовый

сервер, пересылающий любую исходящую почту

в локальный ящик пользователя student. В PostgreSQL нет

встроенной функции для отправки писем, но ее можно

реализовать на каком-либо недоверенном языке.

Убедитесь, что письмо не может быть отправлено до того,

как транзакция покупки будет завершена.

1. Для отправки пользуйтесь уже готовой функцией public.sendmail

(посмотрите ее определение) или напишите свою.

Посылать письмо внутри транзакции покупки неправильно: транзакция

может быть оборвана по какой-либо причине, а письмо уже уйдет.

Воспользуйтесь механизмом фоновых заданий: в транзакции

добавляйте задание на отправку письма. В таком случае оно будет

отправлено, только если транзакция завершится успешно.

Практика

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;

Обратите внимание, что обращение к параметрам возможно только по

номеру.