Хранимые процедуры на Python в PostgreSQL

Рассматриваем несколько простых примеров, чтобы научиться работать с хранимыми процедурами PostgreSQL на Python.

13207

Один из аспектов расширяемости постгреса — это интерфейс для добавления процедурных языков (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!»

			DO $$
     plpy.notice('Hello World!', hint="Будь здоров", detail="В деталях")
$$ LANGUAGE plpython2u;
NOTICE:  Hello World!
DETAIL:  В деталях
HINT:  Будь здоров
DO

Сообщения 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:

			DO $$
     DECLARE a int;
     BEGIN
          SELECT count(*) INTO a FROM pg_class;
     END;
$$ LANGUAGE plpgsql;
0.7 ms

На PL/Python:

			DO $$
     x = plpy.execute('SELECT count(*) FROM pg_class');
$$ LANGUAGE 
plpython2u;
0.8 ms

Это был Python 2. На Python 3 это выглядит так же, но может быть процентов на 10 медленнее.

Но это как-то очень быстро, практически ноль. Попробуем выполнить запрос 1 миллион раз, вдруг разница будет заметней:

На PL/pgSQL:

			DO $$
     DECLARE a int; i int;
     BEGIN FOR i IN 0..999999 LOOP
          SELECT count(*) INTO a FROM pg_class;
    END LOOP;
END;
$$ LANGUAGE plpgsql;
53s

На PL/Python 3:

			DO $$
     for i in range (0,1000000) :
          plpy.execute('SELECT count(*) FROM pg_class')
$$ LANGUAGE plpython3u;
98s

PL/pgSQL уже раза в два быстрее PL/Python. Но опытные постгресисты поймут: что-то не так. PL/pgSQL умеет автоматически кешировать планы запросов, а в PL/Python каждый раз запрос планировался заново. По-хорошему, запросы надо один раз подготавливать, строить план запроса, а потом уже по этому плану их исполнять столько раз, сколько потребуется. В PL/Python можно явно работать с планами запросов. Например, так:

			DO $$
     h = plpy.prepare('SELECT count(*) FROM pg_class')
     for i in range (0,1000000): 
           plpy.execute(h)
$$ LANGUAGE plpython3u;
62s

Теперь мы видим, что производительность языков почти сравнялась — уперлась в работу с базой. Чтобы сравнить языки между собой в чистом виде, попробуем вычислить на них что-нибудь, не обращаясь к базе, например, сумму квадратов.

PL/pgSQL:

			DO $$
     DECLARE i bigint; a bigint;
     BEGIN a=0;
     FOR i IN 0..1000000 LOOP
          a=a+i*i::bigint;
     END LOOP;
END;
$$ LANGUAGE plpgsql;
280ms

PL/Python 3:

			DO $$
a=0
for i in range(1,1000001): a=a+i*i
$$ LANGUAGE plpython3u;
73ms

PL/Python догнал и перегнал PL/pgSQL, для вычислений он оказался примерно в четыре раза быстрее.

Как передавать параметры и возвращать результат из функции на PL/Python ? В общем, как обычно. Проблема только в несоответствии между богатым набором типов данных Postgres и довольно скромным — в Python. Числовые и строковые типы передаются нормально. Массивы — тоже. А вот данные типа JSON (JSONB) придут в Python текстовой строкой, и ее придется распарсить, чтобы с этими данными работать.

Или воспользоваться функциональностью TRANSFORM для настройки пользовательских преобразований типов. Чтобы JSONB адекватно передавался в PL/Python и обратно, нужно установить расширение jsonb_plpython и указать параметр TRANSFORM при создании функции:

			CREATE EXTENSION jsonb_plpython3u;


CREATE FUNCTION func( ……) RETURNS ….
LANGUAGE plpython3
TRANSFORM FOR TYPE jsonb …;

Если Вы используете jsonb c PL/Python, этот TRANSFORM крайне рекомендуется применять.

TRANSFORM — сравнительно новая фича, и поэтом их совсем немного. Для Python, например, поддерживаются только типы jsonb и hstore. Так что есть чем заняться. Для jsonb, кроме того, можно было бы разработать «lazy transform» — который не разворачивал бы весь объект целиком, а только запрашиваемые части. Это заметно ускорило бы обработку больших JSON’ов. Так что задачи для энтузиастов open source разработки есть, добро пожаловать!

13207