18.05.2020 06:07
x-wao 5 4500 Источник
Postgres знаменит своей расширяемостью, что относится и к поддержке процедурных языков (PL). Никто не может похвастаться языком списком языков такой длины, а потенциально этот список и вовсе не ограничен: для того, чтобы подключить язык к серверу, не требуется сверхусилий. Можно даже придумать собственный язык и сделать его серверным процедурным языком. Переделок в СУБД это не потребует. Как и многое другое, такая расширяемость была заложена с самого начала в архитектуру Postgres.

Можно и иногда нужно писать PL-языки под задачи. А еще лучше, если кто-то напишет такой фреймворк для написания языков, чтобы можно было писать не на C, а выбрать более комфортный для разработчика языков язык. Как с FDW, которые можно писать на Python.

Эта статья написана на основе ряда докладов и мастер-классов по этой теме, делавшихся автором на конференциях PgConf.Russia 2019, PgConf.Russia 2018 и DevConf 2017.

Речь пойдет не об экзотике, а о самых распространённых процедурных языках PL/Perl, PL/Python и PL/V8 (то есть JavaScript) и сравнении их возможностей с PL/pgSQL.

Когда такие языки стоит применять? Когда не хватает SQL и PL/pgSQL?

  • Тогда, когда нужно работать со сложными структурами, с алгоритмами: обходом деревьев, например, или когда требуется парсинг HTML или XML, тем более их извлечение из архивов;
  • Когда нужно динамически формировать сложные SQL (отчеты, ORM). На PL/pgSQL не только неудобно, но и будет в некоторых случаях медленнее работать;
  • Если у вас уже много написанных полезных библиотек на Perl или Python, а также если надо использовать библиотеки на C/C++, к которым есть обёртка на Perl или Python или её легко написать. Через хранимые процедуры удобно получать доступ к этим библиотекам. Допустим, вы мигрируете с Oracle. Там есть функция отправки письма, а в самом Postgres такой функции нет. Но в библиотеках Perl и Python их сотни.
  • Еще одна причина — работа с внешними данными. Но это относится, конечно, только к untrusted-языкам (что это — см. ниже), то есть к Perlu и Python(3)u, а не к PL/V8. В обычном Postgres для того, чтобы обратиться к внешним данным, обычно используют FDW, но это специфический интерфейс, он не всегда удобен и не для всего годится. В процедурных языках руки у вас развязаны. Полная свобода!
  • И еще: если вы собрались написать нечто на C, то можно сделать прототип на этих, более приспособленных к быстрой разработке, языках.

Как встроить язык в Postgres


Для реализации языка надо: написать на C от одной до трёх функций:

  • HANDLER — обработчик вызова, который будет исполнять функцию на языке (это обязательная часть);
  • INLINE — обработчик анонимных блоков (если вы хотите, чтобы язык поддерживал анонимные блоки);
  • VALIDATOR — функцию проверки кода при создании функции (если вы хотите, чтобы такая проверка делалась).

Об этом подробно написано в документации здесь и здесь.

«Языки из коробки» и другие языки


Языков, которые поддерживаются «из коробки», всего четыре: PL/pgSQL, PL/Perl, PL/Python и PL/Tcl, но тикль это скорее дань истории: им сейчас мало кто пользуется, больше говорить о нём не будем.
PL/Perl, PL/Python и, конечно, PL/pgSQL поддерживаются Postgres-сообществом. Поддержка других, внекоробочных, языков ложится на их мейнтейнеров — компании, сообщества, или конкретных разработчиков, заинтересованных в том, чтобы язык работал внутри СУБД. PL/V8 продвигает Google. Но время от времени возникают основания сомневаться в безоблачном будущем PL/V8. Нынешний мейнтейнер проекта PL/V8 от Google, Джерри Сиверт (Jerry Sievert) подумывает о том, чтобы поддержка серверного JS в Postgres базировалась на другом движке (например, QuickJS), так как PL/V8 сложно собирать, он требует выкачать 3-5 ГБ всякой всячины на Linux при сборке, и это часто приводит к проблемам на разных ОС. Но PL/V8 широко используется и насквозь оттестирован. Не исключено, что появится PL/JS как альтернатива с другим движком JS, или пока просто как название, к которому будем привыкать в переходный период.

PL/Java используется достаточно редко. У меня лично не возникало потребности писать на PL/Java потому, что в PL/Perl и в PL/V8 достаточно функциональности практически для всех задач. Даже Python особенно не прибавляет возможностей. PL/R полезен для тех, кто занимается статистикой и любит этот язык. О нем мы здесь говорить тоже не будем.

Популярные языки не обязательно популярны у пишущих хранимки: PL/PHP есть, но сейчас практически никем не поддерживается — писать на нём серверные процедуры желающих мало. С языком PL/Ruby почему-то та же картина, хотя язык, казалось бы, более современный.

Процедурный язык на базе Go развивается, см. PL/Go, а также, похоже PL/Lua. Надо будет поизучать их. Для упоротых фанатов шелла есть даже PL/Sh, трудно даже представить, для чего он может понадобиться.

Есть как минимум один доменно-специфический процедурный язык (DSL), узко специализированный под свою задачу — PL/Proxy, который раньше был очень популярен для проксирования и распределения нагрузки на серверы.

В этой статье мы рассмотрим основные, наиболее часто используемые языки. Это, конечно, PL/PgSQL, PL/Perl, PL/Python и PL/V8, ниже будем называть их PL/*.

Языки «из коробки» действительно почти буквально устанавливаются из коробки — обычно инсталляция проходит безболезненно. А вот установить PL/V8, если Вы не нашли в репозитории своей ОС пакета с нужной версией, это почти подвиг, потому что для этого придется фактически собрать весь V8, или, другими словами, Chromium. При этом с сайта google.com вместе с самим V8 будет выкачана вся инфраструктура разработки — рассчитывайте на пару гигабайт трафика. Для Postgres 11 под Ubuntu пакет PL/V8 до сих пор ещё не появился, в репозитории пока доступна только V8 для PG 10. Если хотите, собирайте руками. Важно ещё то, что версия, которую Вы найдете в репозитории, скорее всего довольно-таки старая. На момент публикации статьи последней версией является 2.3.14.

После того, как сам язык установлен, надо еще и «создать» язык — зарегистрировать его в системном каталоге. Это следует делать командой

CREATE EXTENSION plperl;

(вместо plperl можно подставить название другого языка, есть определенные нюансы, см. ниже).
Смотрим, что получилось:

test_langs=# \x
test_langs=# \dL+
List of languages
-[ RECORD 1 ]-----+---------------------------------
Name              | plperl
Owner             | postgres
Trusted           | t
Internal language | f
Call handler      | plperl_call_handler()
Validator         | plperl_validator(oid)
Inline handler    | plperl_inline_handler(internal)
Access privileges |
Description       | PL/Perl procedural language
-[ RECORD 2 ]-----+---------------------------------
Name              | plpgsql
Owner             | postgres
Trusted           | t
Internal language | f
Call handler      | plpgsql_call_handler()
Validator         | plpgsql_validator(oid)
Inline handler    | plpgsql_inline_handler(internal)
Access privileges |
Description       | PL/pgSQL procedural language
[ RECORD 3 ]-----+---------------------------------
Name              | plv8
Owner             | postgres
Trusted           | t
Internal language | f
Call handler      | plv8_call_handler()
Validator         | plv8_call_validator(oid)
Inline handler    | plv8_inline_handler(internal)
Access privileges |
Description       |

PL/pgSQL специально создавать не надо, он всегда уже имеется в базе.

Внимание! PL/pgSQL не надо путать с SQL. Это другой язык. Впрочем, на обычном SQL в Postgres тоже можно писать функции.

Стандарты


В мире СУБД часто говорят о соответствии стандартам SQL. В процедурных языках тоже есть стандарты, хотя говорят о них не так уж часто. Стандарту SQL/PSM в высокой степени соответствует процедурный язык DB2. Реализация его далека от PL/pgSQL, хотя концептуально они близки.

SQL/JRT – стандарт для Java-процедур, и PL/Java ему неплохо соответствует.

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


Процедурные языки в Postgres бывают доверенными (TRUSTED) и недоверенными (UNTRUSTED).
В TRUSTED-языках отсутствует возможность прямой работы с I/O, в том числе с сетью, да и вообще с системными ресурсами. Поэтому такие функции может создавать любой пользователь БД, испортить что-либо и узнать лишнее он не сможет. Функции на UNTRUSTED-языках может создать только суперюзер.

Если интерпретатор языка поддерживает такие ограничения, то на его базе можно создать и TRUSTED, и UNTRUSTED язык. Так с Perl, поэтому существуют разные языки plperl и plperlu. Буква u на конце выдает недоверенный характер языка. Python существует только в недоверенном варианте. PL/v8 — наоборот, только в доверенном. Как следствие, PL/v8 не может подгружать никаких модулей или библиотек с диска, только из БД.

Функция на UNTRUSTED-языке может всё: послать письмо, пингануть сайт, зайти в чужую базу, выполнить HTTP-запрос. TRUSTED-языки ограничены обработкой данных из базы.

К TRUSTED относятся: plpgsql, plperl, plv8, pljava.

К UNTRUSTED относятся: plperlu, pljavau, plpython2u, plpython3u.

Обратите внимание: не существует PL/Python как TRUSTED (так как там нельзя задать ограничения на доступ к ресурсам), а PLpgSQL и PL/V8 — наоборот: не бывают UNTRUSTED.

А вот Perl и Java доступны в обоих вариантах.

PL/pgSQL vs PL/*


Код на PL/pgSQL нативным образом работает со всеми типами данных, которые есть в Postgres. В других языках многих типов Postgres нет, и интерпретатор языка заботится о преобразовании данных во внутреннее представление языка, заменяя непонятные типы текстом. Впрочем, ему можно помочь с помощью TRANSFORM, о котором я расскажу ближе к концу статьи.

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

Поскольку PL/pgSQL TRUSTED, из него нельзя работать с сетью и дисками.

Если речь идет о работе с вложенными структурами данных, в распоряжении PL/pgSQL есть только инструменты Postgres для работы с JSON, весьма громоздкие и непроизводительные, в других же языках работа с вложенными структурами гораздо проще и экономичнее.

В PL/* свое управление памятью, и за памятью надо следить, а может быть и ограничивать ее.

Надо внимательно следить за обработкой ошибок, которая у всех тоже разная.

Зато в PL/* существует глобальный контекст интерпретатора, и его можно использовать, например, для кеширования данных, в том числе планов запросов. Если язык UNTRUSTED, то доступны сеть и диск(и). С базой все эти языки работают, как правило, через SPI, но об этом чуть позже.

Рассмотрим немного подробнее особенности языков PL/*.

PL/Perl


Интерпретатор Perl – это здоровенный кусок кода в памяти, но он, к счастью, создается не при открытии соединения, а только тогда, когда запускается первая хранимая процедура/функция PL/Perl. При его инициализации выполняется код, прописанный в параметрах конфигурации Postgres. Обычно при этом подгружаются модули и делаются предвычисления.

plperl.on_init= 'use Data::Dumper;'
plperl.on_plperl_init= ' ... '
plperl.on_plperlu_init= ' ... '
plperl.use_strict= on

Если вы дописали в конфигурационный файл при работающей базе, заставьте Postgres перечитать конфигурацию. В этой статье в примерах используется модуль Data::Dumper для визуализации структур данных.

Есть параметры для раздельной инициализации TRUSTED и UNTRUSTED Perl и, конечно, параметр use_strict=on. Те, кто программируют на Perl, знают, что без strict это не язык, а одно недоразумение.

PL/Python


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

В отличие от Perl, питону нельзя указать, что делать при инициализации. Еще неудобство: однострочники делать неудобно.

Во всех питоновских функциях определено два словаря – статический SD и глобальный GD. Глобальный позволяет обмениваться данными всем функциям внутри одного бэкенда — что привлекательно и опасно одновременно. Статический словарь у каждой функции свой.

В PL/Python можно делать подтранзакции, о которых мы расскажем ниже.

PL/V8


Он бывает только TRUSTED.

Удобно, что данные JSON автоматически преобразуются в структуру JS. В PL/V8, как и в PL/Python, можно делать подтранзакции. Есть интерфейс для упрощенного вызова функций. Это единственный из рассматриваемых процедурных языков, на котором можно определять оконные функции. Подсказывают, что их можно определять ещё на PL/R, но этот язык вне рамок данной статьи.

И только в PL/V8 есть execution timeout. Правда, по умолчанию он не включен, и если вы собираете PL/V8 руками, то надо при сборке сказать, чтобы он был включен, и тогда вы сможете параметром конфигурации устанавливать таймауты на вызовы функций.

Инициализация у PL/V8 выглядит интересно: поскольку он trusted, то не может прочитать библиотеку с диска, он вообще не может ниоткуда ничего грузить. Все нужное он может взять только из базы. Поэтому определяют хранимую функцию-инициализатор, которая вызывается при старте интерпретатора языка. Имя функции указывается в специальном параметре конфигурации:

plv8.start_proc=my_init # (имя PL/V8-функции)

При инициализации глобальные переменные и функции можно создавать, присваивая их значения атрибутам переменной this. Например, так:

CREATE OR REPLACE FUNCTION my_init()
RETURNS void LANGUAGE plv8 AS $$
     this.get_57 = function() { return 57; }; // создаем глобальную функцию
     this.pi_square = 9.8696044;  // создаем глобальную переменную
$$;
SET plv8.start_proc = 'my_init';
DO LANGUAGE plv8 $$
     plv8.elog(NOTICE, pi_square, get_57() );
$$;

Сравнение PL/Perl vs PL/Python vs PL/V8 на практике


Hello World!


Выполним нехитрое упражнение с выводом этой фразы на всех трех языках, сначала на PL/Perl. И пусть он делает еще что-нибудь полезное, например сообщает свою версию:

DO $$
     elog(NOTICE,"Hello World! $]");
$$ LANGUAGE plperl;

NOTICE:  Hello World!
DO

Можно также использовать обычные для Perl функции warn и die.

Теперь на PL/Python. Точнее на PL/Python3u (untrusted) — для определенности.

DO $$
     import sys
     plpy.notice('Hello World! ' , hint="Будь здоров", detail=sys.version_info)
$$ LANGUAGE plpython3u;


NOTICE:  Hello World! 
DETAIL:  sys.version_info(major=3, minor=6, micro=9, releaselevel='final', serial=0)
HINT:  Будь здоров
DO

Можно использовать throw 'Errmsg'. Из сообщений Postgres вообще можно много чего извлечь: они содержат Hint, Details, номер строки и много других параметров. В PL/Python их можно передать, а в других рассматриваемых языках — нет: их средствами можно выругаться только обычной текстовой строчкой.

В PL/Python на каждый уровень логирования постгреса есть своя функция: NOTICE, WARNING, DEBUG, LOG, INFO, FATAL. Если это ERROR, то свалилась транзакция, если FATAL, свалился весь бэкенд. До PANIC дело, к счастью, не дошло. Почитать можно здесь.

PL/V8

В этом языке Hello world очень похож на перловый. Можно бросить exception с помощью throw, и это тоже будет обработкой ошибки, хотя средства и не столь развитые, как в Python. Если написать plv8.elog(ERROR), эффект будет, кстати, тот же.

DO $$
     plv8.elog(NOTICE, 'Hello World!', plv8.version);
$$ LANGUAGE plv8;

NOTICE:  Hello World! 2.3.14
DO

Работа с базой


Посмотрим теперь, как из хранимых процедур работать с базой данных. В Postgres есть SPI (Server Programming Interface). Это набор функций на C, который доступен всем авторам расширений. Почти все PL-языки предоставляют к SPI свои обертки, но каждый язык делает это немного по-своему.

Функция, написанная на C, но использующая SPI, скорее всего не даст существенного выигрыша по сравнению с PL/PgSQL и другими процедурными языками. Но функция на C, минующая SPI и работающая с данными без посредников (например table_beginscan/heap_getnext), будет работать на порядок быстрее.

PL/Java тоже использует SPI. Но работа с базой все равно происходит в стиле JDBC и по стандарту JDBC. Для создателя кода в PL/Java всё происходит как будто вы работаете из клиентского приложения, но JNI (Java Native Interface) транслирует обращения к базе в те же SPI-функции. Это удобно, и нет принципиальных препятствий воплотить этот принцип в PL/Perl и PL/Python, но это почему-то не сделано, и в планах пока не видно.

Конечно, при желании к чужим базам можно сходить обычным способом – через DBI или Psycopg. Можно и к локальной базе, но зачем.

Если не влезать в холиварную тему «обрабатывать в базе vs обрабатывать на клиенте», и сразу исходить из максимума обработки ближе к данным (хотя бы для того, чтобы не гонять гигантские выборки по сети), то решение использовать функции, сохраненные на сервере, выглядит естественно.

Производительность: надо помнить, что SPI имеет некоторые издержки, и работа SQL-запросов в функциях может оказаться медленнее, чем без функций. В 13-й постгрес вошел патч Константина Книжника, который сокращает эти издержки. Но, конечно, обработка результатов запроса в хранимой функции не требует передачи результата на клиент, и поэтому может оказаться выгодной с точки зрения производительности.

Безопасность: набор отлаженных и проверенных функций изолирует от пользователя структуру базы данных, охраняет от SQL-инъекций и прочих злодейств. Иначе это останется головной болью каждого разработчика приложения.

Повторное использование кода: если с базой работает большое число замысловатых приложений, то удобно хранить полезные функции на сервере, а не писать их в каждом приложении заново.

Как и в каком виде мы получаем данные из базы


В Perl всё просто и понятно. Вызов spi_exec_query возвращает количество обработанных строк, статус и массив строк, которые выбраны SQL-запросом:

DO $$ 
     warn Data::Dumper::Dumper(
          spi_exec_query('SELECT 57 AS x')
     )
$$ LANGUAGE plperl;

WARNING:  $VAR1 = {
          'rows' => [
                    {
                      'x' => '57'
                    }
                  ],
          'processed' => 1,
          'status' => 'SPI_OK_SELECT'
        };

В Python запрос и результат выглядят примерно так же, но здесь функция возвращает не структуру данных, а специальный объект, с которым можно работать по-разному. Обычно он притворяется массивом и, соответственно, из него можно извлекать строки.

DO $$ 
     plpy.notice(
          plpy.execute('SELECT 57 AS x')
     )
$$ LANGUAGE plpython3u;

NOTICE:  <PLyResult status=5 nrows=1 rows=[{'x': 57}]>
DO

А сейчас возьмем 1-ю строчку, достанем оттуда X и получим значение – число.

DO $$ 
     plpy.notice(
          plpy.execute('SELECT 57 AS x')[0]['x']
      )
$$ LANGUAGE plpython3u;

NOTICE:  57
DO

В PL/V8:

DO $$ 
     plv8.elog(NOTICE, JSON.stringify(
          plv8.execute('SELECT 57 as x'))
     );
$$ LANGUAGE plv8;

NOTICE:  [{"x":57}]
DO

Для того, чтобы посмотреть структуру, мы использовали библиотечную функцию JSON.stringify, которую не надо подгружать специально, она уже готова к употреблению в составе PL/v8 по умолчанию.

Экранирование


Чтобы не было злокозненных SQL-инъекций, некоторые символы в запросах надо экранировать. Для этого, во-первых, есть функции SPI и соответствующие им функции (написанные на C) в языках, работающие, как обертки SPI. Например, в PL/Perl:

quote_literal – берет в апострофы и удваивает ' и \. Предназначена для экранирования текстовых данных.
quote_nullable – то же, но undef преобразуется в NULL.
quote_ident – берет в кавычки имя таблицы или поля, если надо. Полезно в случае, когда вы конструируете SQL-запрос и подставляете в него имена объектов базы.

PL/Perl

DO $$
     warn "macy's";
     warn quote_literal("macy's");
$$ LANGUAGE plperl;

WARNING:  macy's at line 2.
WARNING:  'macy''s' at line 3.
DO

Имейте в виду: название таблицы надо экранировать не так, как текстовую строчку. Именно поэтому есть функция quote_ident.

Но в PL/Perl есть и другие функции для экранирования данных отдельных постгресовых типов:

encode_bytea
decode_bytea
encode_array_literal
encode_typed_literal
encode_array_constructor

Функция quote_typed_literal должна принимать любой тип и превращать нетипичные сомнительные символы во что-то заведомо безопасное. Она работает с огромным числом типов, но, всё же, не со всеми. Она, например, не поймет диапазонные типы и воспримет их просто как текстовые строки.

DO $$
     warn encode_typed_literal(
          ["один", "двадцать один"], "text[]"
     );
$$ LANGUAGE plperl;

WARNING:  {один,"двадцать один"} at line 2.
DO

В PL/Python есть три аналогичных функции, и работают они примерно так же:

plpy.quote_literal
plpy.quote_nullable
plpy.quote_ident

DO $$ plpy.notice(
     plpy.quote_literal("Macy's"));
$$ LANGUAGE plpython3u;
NOTICE:  'Macy''s'
DO

Если ли такие же фукнции и в PL/V8?

Конечно! Всё одинаково с точностью до синтаксических особенностей.

plv8.quote_literal
plv8.quote_nullable
plv8.quote_ident

DO $$
    plv8.elog(NOTICE, plv8.quote_nullable("Macy's"));
$$ LANGUAGE plv8;

NOTICE:  'Macy''s'

Производительность


Какой язык самый быстрый? Обычно отвечают: C. Но правильный ответ — C или SQL. Почему SQL? Дело в том, что функция на этом языке не всегда выполняется явно. Она может заинлайниться в запрос (планировщик встроит функцию в тело основного запроса), уже вместе с запросом хорошо соптимизироваться, в результате получится быстрее. Но при каких условиях код может встраиваться в запрос? Есть несколько простых условий, о которых можно прочитать, скажем,здесь. Например, функция не должна исполнятся с правами владельца (быть SECURITY DEFINER). Большинство простых функций подойдут под эти условия.

В этой статье мерить будем «на коленке», не всерьёз. Нам нужно приблизительное сравнение. Сначала включим тайминг:

\timing

Попробуем SQL (Времена выполнения команд, которые приводятся ниже — это округленные средние значения, которые автор получал на незагруженном шестилетнем ПК. Их можно сравнивать между собой, но на научную точность они не претендуют):

SELECT count(*) FROM pg_class;
0.5 ms

Работает очень быстро. В других языках тратится время на вызов функций из языка. Разумеется, первый раз запрос будет выполняться медленнее из-за инициализации интерпретатора. Потом стабилизируется.

Попробуем PL/pgSQL:

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

PL/Perl:

DO $$
     my $x = spi_exec_query('SELECT count(*) FROM pg_class');
$$ LANGUAGE plperl;
0.7 ms

PL/Python:

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

Это был Python 2. Теперь Python 3 (напоминаю: Python2 и Python3 не живут мирно в пределах одной сессии, возможен конфликт по именам):

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

И, наконец, PL/V8:

DO $$
     var x = plv8.execute('SELECT count(*) FROM pg_class');
$$ LANGUAGE plv8 ;
0.9 ms

Но это как-то очень быстро. Попробуем выполнить запрос 1000 раз или 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/Perl:

DO $$
     for (0..999999) {
          spi_exec_query('SELECT count(*) FROM pg_class');
     }
$$ LANGUAGE plperl;
102s

PL/Python 3:

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

PL/V8:

DO $$
     for(var i=0;i<1000;i++)
          plv8.execute('SELECT count(*) FROM pg_class');
$$ LANGUAGE plv8;
100ms

Обратите внимание, что с PL/V8 эксперимент проведен с тысячей, а не миллионом итераций. При умеренных ресурсах PL/V8 в цикле из 1млн операций съест всю память и вовсе повесит машину. Уже на тысяче итераций процесс postgres отбирает 3.5ГБ памяти и 100% записи на диск. На самом деле postgres запускает среду V8, и память ест, конечно, она. После исполнения запроса отдавать память назад этот турбо-монстр не собирается. Чтобы высвободить память, приходится закончить сессию.

Видим, что PL/pgSQL уже раза в 2 быстрее PL/Perl и PL/Python. PL/V8 пока чуть отстает от них, но ближе к концу статьи он частично реабилитируется.

Вообще, Perl с Python'ом в этих экспериментах показывают примерно одинаковые результаты. Раньше Perl немного уступал Python'у, в современных версиях он чуточку быстрее. Третий питон чуть медленнее второго. Вся разница в пределах 15%.

Производительность с PREPARE


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

DO $$
     my $h = spi_prepare('SELECT count(*) FROM pg_class');
     for (0..999999) {
          spi_exec_prepared($h);
     }
     spi_freeplan($h);
$$ LANGUAGE plperl;
60s

PL/Python 3:

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

PL/V8:

DO $$
     var h=plv8.prepare('SELECT count(*) FROM pg_class');
     for(var i=0;i<1000;i++) h.execute();
$$ LANGUAGE plv8;
53ms

С prepare наши два языка практически догнали PL/pgSQL, а третий — тоже хотел, но не дошел до финиша из-за растущих потребностей в памяти.

Но если не учитывать память, то видно, что все языки идут практически ноздря в ноздрю – и не случайно. Узкое место у них сейчас общее – работа с базой через SPI.

Производительность вычислений


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

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/Perl:

DO $$
     my $a=0;
     for my $i (0..1000000) { $a+=$i*$i; };
     warn $a;
$$ LANGUAGE plperl;
63ms

PL/Python 3:

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

PL/V8:

DO $$
     var a=0;
     for(var i=0;i<=1000000;i++) a+=i*i;
     plv8.elog(NOTICE, a);
$$ language plv8;
7.5ms

Видим, что PL/Perl и PL/Python догнали и перегнали PL/pgSQL, они раза в 4 быстрее. А восьмерка рвёт всех! Но неужели это задаром? Или мы получим за это по голове? Да, получим.

Число в JavaScript – это float, и результат получается быстро, но не точно: 333333833333127550 вместо 333333833333500000.

Вот формула, по которой считается точный результат:

? = n*(n+1)*(2n+1)/6

В качестве упражнения можете доказать её с помощью математической индукции.

В порядке смеха, 

DO LANGUAGE plv8 $$
plv8.elog(NOTICE, parseInt(33333383333312755033)) $$;

NOTICE:
33333383333312754000

В Javascript parseInt все равно делает float, а не Int.

Всё же в V8 в 2018 г. появился BigInt, и считать теперь можно точно, но с ущербом для скорости, поскольку это не 64-разрядное целое, а целое произвольной разрядности. Впрочем, в PL/V8 это новшество пока не попало. В других процедурных языках числа произвольной разрядности (аналоги SQL-ного numeric) поддерживаются через специальные библиотеки.

В Perl для этого есть модуль Math::BigFloat для арифметики с произвольной точностью, а в Python – пакет Bigfloat – обертка Cython вокруг библиотеки GNU MPFR.

Производительность функции для сортировки


Вот практический пример, на котором видна разница производительности сортировки по функции, если эта функция пишется на разных языках. Задача: отсортировать текстовые поля, содержащие номера выпусков журнала, которые могут быть такими:

1
2
3
4-5
6
6A
6Б
11
12

Т.е. вообще-то это строка, но она начинается с числа, и сортировать надо по этим числам. Поэтому, чтобы корректно сортировать как строки, дополним числовую часть нулями слева, чтобы получилось:

0000000001
0000000002
0000000003
0000000004-5
0000000006
0000000006A
0000000006Б
0000000011
0000000012

Да, я знаю, что это не единственное решение задачи (и даже не совсем правильное). Но для примера оно подойдёт.

Для запроса типа SELECT ... ORDER BY nsort(n) напишем функции на PL/Perl, SQL, PL/Python и PL/V8 приводящие номера журналов к этому виду:

CREATE OR REPLACE FUNCTION nsort(text) RETURNS text 
   LANGUAGE PLPERL IMMUTABLE AS $$
    my $x = shift;
    return ($x =~ /^\s*(\d+)(.*)$/)
        ? sprintf("%010d", $1).$2
        : $x;
$$;

CREATE OR REPLACE FUNCTION _nsort(x text) RETURNS text
     LANGUAGE SQL  IMMUTABLE  AS $$
 WITH y AS (
    SELECT regexp_match(x,'^\s*(\d*)(.*)$') as z
 )
 SELECT CASE WHEN z[1] = '' THEN x ELSE lpad(z[1],10,'0') || z[2] END FROM y;
$$;

CREATE OR REPLACE FUNCTION py_nsort(x text) RETURNS text 
   LANGUAGE plpython2u IMMUTABLE AS $$
import re
r = re.match('^\s*(\d+)(.*)$', x)
return x if r == None else ('%010d' % int(r.group(1))) + r.group(2)
$$;

CREATE OR REPLACE FUNCTION js_nsort(x text) RETURNS text 
   LANGUAGE plv8 IMMUTABLE AS $$
var m = x.match(/^\s*(\d+)(.*)$/);
if(m) { return m[1].padStart(10-m[1].length,'0') + m[2]; }
else { return x; } 
$$;

На моей библиотеке из 15.5 тысяч журнальных статей запрос с использованием функции на PL/Perl занимает около 64мс против 120мс на PL/Python и 200мс на PL/PgSQL. Но быстрее всех — PL/v8: 54мс.

Примечание: экспериментируя с сортировкой, обеспечьте нужный объем рабочей памяти, чтобы сортировка шла в памяти (EXPLAIN тогда покажет Sort Method: quicksort). Объем памяти устанавливается параметром work_mem:

set work_mem = '20MB';

Память


Perl не любит зацикленные структуры, он не умеет их очищать. Если у вас в a есть указатель на b, в в b указатель на a, то счетчик ссылок никогда не будет обнулятся, и память не освободится.

В языках со сборкой мусора другие проблемы. Неизвестно, например, когда память освободится, и освободится ли вообще. Или — если не позаботиться об этом специально — сборщики отправятся собирать мусор в самый неподходящий момент.

Но есть и особенности управления памятью, связанные непосредственно с Postgres. Есть структуры, которые аллоцирует SPI, а Perl не всегда догадывается, что их надо освобождать.

PL/Perl

Вот так НЕ течёт:

CREATE OR REPLACE function cr()
RETURNS int LANGUAGE plperl AS
$$
     return spi_exec_query(
           'SELECT count(*) FROM pg_class'
     )->{rows}->[0]->{count};
$$;

А вот так течёт:

CREATE OR REPLACE function cr()
RETURNS int LANGUAGE plperl AS
$$
     my $h = spi_prepare(
          'SELECT count(*) FROM pg_class'
     );
     return spi_exec_prepared($h)->{rows}->[0]->{count};
$$;

После исполнения хандлер $h останется жить, несмотря на то, что ни одной живой ссылки на него не останется.

Ничего страшного, просто надо помнить о необходимости явного освобождения ресурсов при помощи spi_freeplan($h):

CREATE OR REPLACE function cr()
RETURNS int LANGUAGE plperl AS
$$
     my $h = spi_prepare(
          'select count(*) from pg_class'
     );
     my $res = spi_exec_prepared($h)->{rows}->[0]->{count};
     spi_freeplan($h);
     return $res;
$$;

PL/Python:

Python не течёт никогда, план освобождается автоматически:

CREATE OR REPLACE function cr3() RETURNS int
LANGUAGE plpythonu as
$$
     return plpy.execute(
           'select count(*) from pg_class'
     )[0]['count']
$$;

PL/V8

Та же история, что и с Perl. Так не течёт:

CREATE OR REPLACE FUNCTION crq() RETURNS int
LANGUAGE plv8 AS
$$
     return plv8.execute(
          'select count(*) from pg_class‘
     )[0].count;
$$;

А вот так течёт:

CREATE OR REPLACE FUNCTION crq() RETURNS int
LANGUAGE plv8 AS
$$
     var h = plv8.prepare(
          'select count(*) from pg_class'
     );
     return h.execute()[0].count;
$$;

Опять же: не стоит забывать об освобождении ресурсов. Здесь это делает h.free();

Так не течёт:

CREATE OR REPLACE FUNCTION crq() RETURNS int
LANGUAGE plv8 AS
$$
     var h = plv8.prepare(
          'select count(*) from pg_class'
     );
     var r = h.execute()[0].count;
     h.free();
     return r;
$$;

Параметры


Пора разбираться с тем, как в функции передаются аргументы. В примерах мы будем передавать в функцию 4 параметра с типами:

  • целое;
  • массив;
  • bytea и
  • jsonb

В каком виде они попадают в PL/Perl?

CREATE OR REPLACE FUNCTION crq(a int, b
bytea, c int[], d jsonb ) RETURNS void
LANGUAGE plperl AS
$$
    warn Dumper(@_);
$$;

SELECT crq(1,'abcd', ARRAY[1,2,3],'{"a":2,"b":3}');


WARNING:  $VAR1 = '1';
$VAR2 = '\\x61626364';
$VAR3 = bless( {
                 'array' => [
                              '1',
                              '2',
                              '3'
                            ],
                 'typeoid' => 1007
               }, 'PostgreSQL::InServer::ARRAY' );
$VAR4 = '{"a": 2, "b": 3}';
 crq 
-----
(1 row)

Будет это JSON или JSONB — в данном случае никакой разницы: они всё равно попадают в виде строки. Это плата за универсальность: в Postgres очень много типов, разной степени «встроенности». Требовать от разработчика, чтобы вместе с новым типом он сразу снабжал и функциями преобразования для всех PL/* было бы перебором. По умолчанию многие типы передаются как строки. Но это не всегда удобно, приходится эти сроки парсить. Конечно, хотелось бы, чтобы данные Postgres сразу превращались в соответствующие структуры Perl. По умолчанию этого не происходит, но начиная с 9.6 появился механизм TRANSFORM – возможность определять функции преобразования типов: CREATE TRANSFORM.

Чтобы создать TRANSFORM, надо написать на C две функции: одна будет преобразовывать данные определеного типа в одну сторону, другая обратно. Обратите внимание, TRANSFORM работает в четырёх местах:

  • При передаче параметров в функцию;
  • При возврате значения функции;
  • При передача параметров в SPI-вызов внутри функции;
  • При получении результата SPI-вызова внутри функции.

В 11-й версии Postgres появились TRANSFORM JSONB для Perl и Python, разработанные Антоном Быковым. Теперь JSONB парсить не нужно, он попадает в Perl сразу как соответствующая структура. Надо создать расширение jsonb_plperl, и тогда можно использовать TRANSFORM:

CREATE EXTENSION jsonb_plperl;
CREATE OR REPLACE FUNCTION crq2(d jsonb)
RETURNS void LANGUAGE plperl
TRANSFORM FOR TYPE jsonb AS $$
     warn Dumper(@_);
$$;

Можно вызвать эту функцию, чтобы убедиться, что JSONB превратился в перловый хэш:

SELECT crq2( '{"a":2,"b":3}');


WARNING:  $VAR1 = {
          'a' => '2',
          'b' => '3'
        };
 crq2 
------
(1 row)

Совсем другое дело!

Автор этой статьи тоже приложил руку к разработке TRANSFORM'ов. Оказалось, что такой простой тип данных, как boolean передаётся в PL/Perl в неудобной форме, в виде текстовых строк 't' или 'f'. Но в понимании Perl строка 'f' это true. Чтобы устранить неудобство, был придуман патч, определяющий преобразование для булевого типа. Этот патч попал в PostgreSQL 13 и скоро можно будет им пользоваться. В виду своей простоты, bool_plperl может служить минимальным стартовым образцом для написания любого другого преобразования.

Надеюсь, что кто-нибудь разработает TRANSFORM и для других типов данных (bytea, массивы, даты, numeric).

Теперь посмотрим, как параметры передаются в Python.

CREATE EXTENSION jsonb_plpython3u;
CREATE OR REPLACE FUNCTION pdump(a int, b bytea, c int[], d jsonb ) RETURNS void
LANGUAGE plpython3u
TRANSFORM FOR TYPE jsonb AS $$
      plpy.warning(a,b,c,d)
$$;

SELECT pdump(1,'abcd', ARRAY[1,2,3],'{"a":2,"b":3}');


WARNING:  (1, b'abcd', [1, 2, 3], {'a': Decimal('2'), 'b': Decimal('3')})
 pdump 
-------
(1 row)

Массив преобразуется в массив — это хорошо (начиная с версии PG10 в питон корректно передаются и многомерные массивы). В Perl массив преобразовывался в объект специального класса. Ну и jsonb трансформировался. Без TRANSFORM jsonb передастся в виде строки.

Теперь посмотрим, в каком виде параметры попадают в JS.

CREATE OR REPLACE FUNCTION jsdump(a int, b bytea, c int[], d jsonb) RETURNS void
LANGUAGE plv8 AS $$
     plv8.elog(WARNING,a,b,c,d)
$$;

SELECT jsdump(1,'abcd', ARRAY[1,2,3],'{"a":2,"b":3}');


WARNING:  1 97,98,99,100 1,2,3 [object Object]
jsdump 
-------
(1 row)

JSONB преобразовался в объект JavaScript без всякого TRANSFORM! ВременнЫе типы Postgres тоже преобразуются в тип Date JS. То же с boolean. Все трансформации уже встроены в PL/V8.

Работа с бесконечностью


Константа INFINITY используется не так уж часто, но неаккуратная работа с ней опасна. В PostgreSQL Infinity и -Infinity существуют как специальные значения некоторых временнЫх типов и типа с плавающей точкой. Но передачу Infinity в процедурные языки и обратно надо обсуждать подробно, так как работа с ними может зависеть не только от языка, а и от библиотек, от ОС и даже от железа.

В Python есть модуль Numpy, определяющий числовую бесконечность:

import numpy as nm
a = nm.inf
b = -nm.inf
print(a, b)

inf -inf

В Perl тоже есть бесконечность, в её качестве используется строка "infinity", которую можно сократить до "inf". Например, можно сказать:

perl -e 'print 1 * "inf"'

Inf

или

perl -e 'print 1/"inf"'

0

В PL/Perl, PL/Python, PL/v8 числовая бесконечность из Postgres передаётся корректно, но бесконечная дата — не совсем. Вернее, в PL/Perl и PL/Python нет встроенного типа данных для времени, туда приходит строка. В PL/V8 встроенный тип Date есть, и обычная дата из постгреса превращается в него. Но бесконечную дату V8 не знает, и при передаче она превращается в Invalid Date.

Передача параметров в prepared-запросы


Вернемся к prepare, рассмотрим, как там передаются параметры. Между разными языками тут много общего, поскольку все они базируются на SPI.

Когда вы подготавливаете запрос в PL/Perl, требуется определить тип параметров, которые передаются, а при выполнении запроса уже указываете только значения этих параметров (точно так же передаются параметры и в PL/pgSQL).

DO LANGUAGE plperl $$
     my $h= spi_prepare('SELECT * FROM pg_class WHERE
          relname ~ $1', 'text' );                     # указываем тип параметра
     warn Dumper(spi_exec_prepared($h, 'pg_language')); # передаем значение параметра
     spi_freeplan($h);
$$;

В PL/Python суть та же, но синтаксис немного отличается:

DO LANGUAGE plpython3u $$
     h= plpy.prepare('SELECT relname FROM pg_class WHERE relname ~ $1', ['text'] )
     plpy.notice(р.execute (['pg_language']))
$$;

В PL/V8 отличия минимальны:

DO LANGUAGE plv8 $$
    var h= plv8.prepare('SELECT relname FROM pg_class WHERE relname ~ $1', ['text'] );
    plv8.elog(NOTICE, h.execute (['pg_language']));
    h.free();
$$;

В PL/Java всё по-другому. Там явно не используется SPI, а формируется псевдо-JDBC коннект к базе. Для программиста на PL/Java всё происходит так же, как если бы он создавал клиентское приложение. Это удобно, и можно было бы также подойти к дизайну PL/Perl и PL/Python, но это почему-то не было сделано (впрочем, никто не запрещает создать ещё пару реализаций PL/Perl и PL/Python).

Работа с курсором


У всех функций SPI, которые мы использовали, когда ходили в базу — spi_exec_query() и др. — есть параметр, ограничивающий количество возвращаемых строк. Если нужных вам возвращаемых строк много, то без курсора не обойтись, чтобы подтягивать их понемногу.

Курсоры работают во всех этих языках. В PL/Perl
spi_exec_query возвращает курсор, из которого можно извлекать строки по одной. Закрывать курсор не обязательно, он сам закроется. Но если вы хотите переоткрывать его заново, можно закрыть его явно командой close().

DO LANGUAGE plperl $$
    my $cursor = spi_query('SELECT * FROM pg_class');
    my $row;
    while(defined($row = spi_fetchrow($cursor))) {
         warn $row->{relname};
    }
$$;

WARNING:  pg_statistic at line 5.
WARNING:  pg_toast_2604 at line 5.
WARNING:  pg_toast_2604_index at line 5.
WARNING:  pg_toast_2606 at line 5.
WARNING:  pg_toast_2606_index at line 5.
WARNING:  pg_toast_2609 at line 5.
WARNING:  pg_toast_2609_index at line 5.
...

В PL/Python всё очень похоже, но курсор представлен как объект, по которому можно ходить циклом:

h = plpy.prepare('SELECT ...');
cursor = plpy.cursor(h);
for row in cursor:
...
cursor.close() // не обязательно

В PL/v8 всё тоже очень похоже, но не забывайте освобождать план подготовленного запроса:

var h = plv.prepare('SELECT ...');
var cursor = h.cursor();
var row;
while(row = cursor.fetch()) {
...
}
cursor.close();
h.free();

PL/V8: Быстрый доступ к функциям


В PL/V8 можно вызвать функцию не из обычного SELECT, а найти её по имени и сразу запустить при помощи plv8.find_function(name);. Но надо учитывать, что в JS функция не может быть полиморфной, как в PostgreSQL, в котором функции с одинаковым именем, но с разными параметрами могут сосуществовать. В PL/v8 мы, конечно, можем создавать полиморфные функции, но при попытке воспользоваться find_function будет ошибка.

ERROR:  Error: more than one function named "jsdump"

Если функция по имени находится однозначно, то её можно вызывать, минуя SPI и преобразования типов, т.е. гораздо быстрее. Например, так:

DO LANGUAGE plv8 $$
plv8.find_function('jsdump')(1, 'abc');
$$;

Транзакции


В Postgres 11 большая радость: появились настоящие процедуры. Раньше в Postgres были только функции. Радость не только из-за совместимости и соответствия стандарту SQL, а вот отчего: внутри процедур можно коммитить и откатывать транзакции.

В PL/Perl и PL/Python уже есть фунции SPI для управления транзакциями, а в PL/V8 этого пока нет. В PL/Perl эти функции называются spi_commit() и spi_rollback(), а пример использования есть в документации. В PL/Python это plpy.commit() и plpy.rollback().

Подтранзакции


Подтранзакции удобны для корректной обработки ошибок в сложной многоуровневой логике.

В PL/pgSQL внутри транзакции каждый блок с ключевым словом EXCEPTION представляет собой подтранзакцию. О некоторых проблемах с производительностью и надежностью, которые могут при этом возникнуть, можно прочитать, например, здесь.

В PL/Perl явных подтранзакций нет, но их можно имитировать через savaepoint'ы. По-видимому, при желании нетрудно написать перловый модуль, реализующий подтранзакции в явном виде.

В PL/Python подтранзакции появились давно: с 9.5 явные, до этого были неявные. Можно определить транзакцию, завернуть ее в try-блок и выполнить. Если подтранзакция сваливается, то мы попадаем в блок except, если не сваливается, то в блок else и идем дальше.

try:
     with plpy.subtransaction():
          plpy.execute("...")
          plpy.execute("...")
except plpy.SPIError, e:
. . .
else:
. . .

Похожая конструкция есть и в PL/V8, только в синтаксисе JS.

try {
plv8.subtransaction(function() {
plv8.execute('UPDATE...');
plv8.execute('UPDATE...');
});
}
catch(e) {
...
}

Заключение


Пробуйте, но не злоупотребляйте :) Определенную пользу знание PL/* может принести. Как и любой инструмент, они любят, когда их используют по назначению.

PL/v8 очень перспективен, но иногда ведет себя неожиданно и имеет ряд проблем. Поэтому лучше брать языки «из коробки», если они подходят для вашей задачи.

Хочу выразить благодарность Игорю Левшину (Igor_Le), который весьма помог мне с подготовкой материала для статьи, и подкинул несколько полезных идей, а также Евгению Сергееву и Алексею Фадееву за предложенные ими коррекции.