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

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

set work_mem = '20MB';

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};
$$;

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;
$$;

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

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;
$$;

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;
$$;

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)

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

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

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

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)