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

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

15К открытий16К показов

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

Следите за новыми постами
Следите за новыми постами по любимым темам
15К открытий16К показов