PostgreSQL: Серверное программирование на «человеческом» языке (PL/Perl, PL/Python, PL/v8)

?v=1

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, похоже, подзавял — последний коммит в начале 2018-го года. Для упоротых фанатов шелла есть даже 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. Если хотите, собирайте руками.

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

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 $$
     plpy.notice('Hello World!', hint="Будь здоров", detail="В деталях")
$$ LANGUAGE plpython3u;
NOTICE:  Hello World!
DETAIL:  В деталях
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!');
$$ LANGUAGE plv8;
NOTICE:  Hello World!
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:  
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, приводящие номера журналов к этому виду:

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) { var pad = ''; for(var i=0,l=10-m[1].length;i


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

Примечание: экспериментируя с сортировкой, обеспечьте нужный объем рабочей памяти, чтобы сортировка шла в памяти (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)
© Habrahabr.ru