Хранимые процедуры на Python в PostgreSQL
Рассматриваем несколько простых примеров, чтобы научиться работать с хранимыми процедурами PostgreSQL на Python.
15К открытий16К показов
Иван Панченко
сооснователь компании Postgres Professional, российского разработчика ПО
Один из аспектов расширяемости постгреса — это интерфейс для добавления процедурных языков (PL). Благодаря этому, мы имеем возможность писать серверные процедуры не только на SQL-подобном PL/PgSQL, но и на обычных императивных языках программирования, в том числе Python. Точнее, PL/Python — это Python с прикрученным к нему SPI — программным интерфейсом сервера, позволяющем питоновским процедурам выполнять запросы в базе данных.
Наряду с PL/Perl и PL/v8 (Javascript) PL/Python является одним из наиболее распространенных не SQL-ных процедурных языков для серверного программирования в Postgres. Он входит в состав основного дистрибутива и присутствует во всех пакетных репозиториях.
В каком случае есть смысл использовать PL/Python и другие не SQL-ные процедурные языки? Когда не хватает возможностей SQL и PL/pgSQL?
- Для работы с алгоритмами и структурами данных, которых нет в SQL. Это может быть обход деревьев, синтаксический разбор, извлечение данных из HTML или XML, какие-либо расчеты, и т.д.
- Для динамического формирования сложных SQL, например для отчетов, или в ORM.
- Для использования имеющегося на Python богатого набора библиотек.
- Для работы с внешними данными, получения данных по сети, из файлов, запуска внешних программ, отправки писем и т.д.
- Для прототипирования расширений, которые Вы планируете затем реализовать на С.
PL/Python является недоверенным (UNTRUSTED) языком. К таким языкам относятся те, которые могут напрямую осуществлять ввод-вывод (работать с диском, сетью и т. п.). По соображениям безопасности создавать функции на таких языках может только суперпользователь базы данных.
Интерпретатор языка PL/Python создается при первом обращении к нему, т. е. при первом вызове функции, процедуры или анонимного блока на PL/Python. И тут важно сразу определиться, какой версии Python вы хотите использовать: второй или третий.
Интерпретаторы Python 2 и Python 3 не уживаются вместе в одном процессе: возникает конфликт по именам. Если вы в одной сессии работали с одним из них, а потом загрузили другой, то Postgres упадет, и для серверного процесса (backend) это будет фатальной ошибкой. Обращение к другой версии возможно, но для этого нужно будет открыть другую сессию.
Из-за различий Python 2 и Python 3 в Postgres определено два разных процедурных языка — plpython2u и plpython3u. Буква u на конце говорит о том, что язык UNTRUSTED.
Во все PL/Python-овских функциях и процедурах определено два словаря — статический SD и глобальный GD. Глобальный позволяет обмениваться данными всем функциям внутри одного бэкенда — что привлекательно и опасно одновременно. Статический словарь у каждой функции свой. Эти словари можно использовать, чтобы сохранять (кешировать) какие-то данные между вызовами функции.
Рассмотрим несколько простых примеров, чтобы научиться использовать PL/Python.
Напишем анонимный блок, который выводит сообщение «Hello World!»
Сообщения Postgres могут содержать помимо самого текста сообщения, поля Hint и Details, номер строки и много других параметров. Также для выдаче текстового сообщения приведёт throw 'Errmsg'
В PL/Python на каждый уровень логирования Postgres есть своя функция: NOTICE, WARNING, DEBUG, LOG, INFO, FATAL. Если это ERROR, то свалилась транзакция, если FATAL, свалился весь бэкенд. До PANIC дело, к счастью, не дошло. Почитать об этом можно здесь.
Теперь попробуем выполнить простейший SQL-запрос, и проверим скорость работы в сравнении с PL/pgSQL. Обратите внимание на функции plpy.* — они как и представляют собой интерес СУБД, имеющийся в PL/Python. Подробнее о них можно прочитать в документации.
На PL/pgSQL:
На PL/Python:
Это был Python 2. На Python 3 это выглядит так же, но может быть процентов на 10 медленнее.
Но это как-то очень быстро, практически ноль. Попробуем выполнить запрос 1 миллион раз, вдруг разница будет заметней:
На PL/pgSQL:
На PL/Python 3:
PL/pgSQL уже раза в два быстрее PL/Python. Но опытные постгресисты поймут: что-то не так. PL/pgSQL умеет автоматически кешировать планы запросов, а в PL/Python каждый раз запрос планировался заново. По-хорошему, запросы надо один раз подготавливать, строить план запроса, а потом уже по этому плану их исполнять столько раз, сколько потребуется. В PL/Python можно явно работать с планами запросов. Например, так:
Теперь мы видим, что производительность языков почти сравнялась — уперлась в работу с базой. Чтобы сравнить языки между собой в чистом виде, попробуем вычислить на них что-нибудь, не обращаясь к базе, например, сумму квадратов.
PL/pgSQL:
PL/Python 3:
PL/Python догнал и перегнал PL/pgSQL, для вычислений он оказался примерно в четыре раза быстрее.
Как передавать параметры и возвращать результат из функции на PL/Python ? В общем, как обычно. Проблема только в несоответствии между богатым набором типов данных Postgres и довольно скромным — в Python. Числовые и строковые типы передаются нормально. Массивы — тоже. А вот данные типа JSON (JSONB) придут в Python текстовой строкой, и ее придется распарсить, чтобы с этими данными работать.
Или воспользоваться функциональностью TRANSFORM для настройки пользовательских преобразований типов. Чтобы JSONB адекватно передавался в PL/Python и обратно, нужно установить расширение jsonb_plpython и указать параметр TRANSFORM при создании функции:
Если Вы используете jsonb c PL/Python, этот TRANSFORM крайне рекомендуется применять.
TRANSFORM — сравнительно новая фича, и поэтом их совсем немного. Для Python, например, поддерживаются только типы jsonb и hstore. Так что есть чем заняться. Для jsonb, кроме того, можно было бы разработать «lazy transform» — который не разворачивал бы весь объект целиком, а только запрашиваемые части. Это заметно ускорило бы обработку больших JSON’ов. Так что задачи для энтузиастов open source разработки есть, добро пожаловать!
15К открытий16К показов