Share This
Связаться со мной
Крути в низ
Categories
//Хранимые процедуры на Python в PostgreSQL

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

20.09.2020Category : Python

hranimye procedury na python v postgresql 72ff4ad - Хранимые процедуры на Python в PostgreSQL

Иван Панченко, сооснователь компании 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) языком. К таким языкам относятся те, которые могут напрямую осуществлять ввод-вывод (работать с диском, сетью и т. п.). По соображениям безопасности создавать функции на таких языках может только суперпользователь базы данных.

PHP-программист

«ООО «ОЛКОН»», Самара, от 30 000 до 90 000 ₽

tproger.ru Вакансии на tproger.ru

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

Интерпретаторы Python 2 и Python 3 не уживаются вместе в одном процессе: возникает конфликт по именам. Если вы в одной сессии работали с одним из них, а потом загрузили другой, то Postgres упадет, и для серверного процесса (backend) это будет фатальной ошибкой. Обращение к другой версии возможно, но для этого нужно будет открыть другую сессию.

Из-за различий Python 2 и Python 3 в Postgres определено два разных процедурных языка — plpython2u и plpython3u. Буква u на конце говорит о том, что язык UNTRUSTED.

Для чего хорош Python?tproger.ru

Во все 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 полезных команд PostgreSQLtproger.ru

Хинт для программистов: если зарегистрируетесь на соревнования Huawei Honor Cup, бесплатно получите доступ к онлайн-школе для участников. Можно прокачаться по разным навыкам и выиграть призы в самом соревновании.

Перейти к регистрации

  • 12 views
  • 0 Comment

Leave a Reply

Ваш адрес email не будет опубликован. Обязательные поля помечены *

Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.

Связаться со мной
Close