ИТ-инфраструктура — это как водопровод, без неё жизнь уже почти невозможна. И в эти безрадостные дни мы продолжаем выпускать Postgresso.
Feature Freeze
7 апреля произошла Заморозка функциональности. Вошедших новшеств много. Традиционный обзор Павла Лузанова выйдет немного позже.
Главными достижениями недавнего времени стали 2 серии мощных патчей, добавляющие важную функциональность и ещё плотней приближающие к стандартам SQL — SQL/JSON и MERGE.
SQL/JSON
Вот два поста в ЖЖ Олега Бартунова:
SQL/JSON standard-2016 conformance for PostgreSQL, Oracle, SQL Server and MySQL
SQL/JSON in PG15 !
Скрин стандартов из статьи Олега Бартунова
Между этими двумя постами три года. В первом он обозначил 3 большие серии патчей, связанных со стандартом SQL/JSON. Во втором — недавние коммиты. Судьба патчей такова:
SQL/JSON: jsonpath (закоммичено на коммитфесте 2019-03)
SQL/JSON: functions (закоммичено на коммитфесте 2022-03)
SQL/JSON: JSON_TABLE (закоммичено на коммитфесте 2022-03)
Это результаты больших трудов многих разработчиков, прежде всего из Postgres Professional, инициировавших эту деятельности и ставших её главной силой. Из всех стоит выделить разработчика Postgres Professional Никиту Глухова. Началось всё ещё в 2016-м, первый патч на тему SQL/JSON был закоммичен в 2017-м.
Ещё одна статья Олега есть в блоге Postgres Professional:
SQL/JSON patches committed to PostgreSQL 15!
Она пока только на английском, перевод должен появиться на днях в русской версии блога (мы сделаем апдейт).
Документация по SQL/JSON в Postgres называется JSON Functions and Operators, но там есть и про Path (раздел 9.16.2), и JSON_TABLE (в разделе 9.16.3.2.5.). Тема достойна отдельной статьи (если не серии статей), а сейчас немного подробней об этих трёх группах патчей.
JSONPath — набор средств адресации данных внутри JSON. О JSONPath можно почитать в нашей статье: Что заморозили на feature freeze 2019. Часть I. JSONPath
SQL/JSON-функции. Их очень много. И ещё не все реализованы. Но это не страшно, главное, что API уже есть, и постепенно все функции появятся.
Появились конструкторы SQL/JSON. Сконструировав, можно проверить:
JSON_EXISTS() — возвращает ли jsonpath-выражение, примененное к JSONB, какие-либо значения.
Но есть ещё и IS JSON. Этот проверочный предикат может использоваться с ARRAY / OBJECT / SCALAR / WITH | WITHOUT UNIQUE KEYS. А можно проверять и так: IS NOT JSON.
Для запросов, использующих JSONPath есть много функций, возвращающих типы SQL.
JSON_VALUE() — возвращает одно значение, а если их много — ошибку.
JSON_QUERY() — возвращает JSON-объект или JSON-массив;
JSON_TABLE — функция делает запрос к JSON(B), а результаты представляет в виде реляционного представления (relational view), к которому можно обращаться как к обычной таблице. JSON_TABLE можно использовать только внутри предложения FROM в запросе SELECT к SQL-таблице.
О TABLE развёрнутый пример в той же статье. Он продолжает тему поиска нужных квартир, на таком JSON демонстрировались возможности JSONPath.
Итого: Эндрю Данстан (Andrew Dunstan, EDB) составил (пока неофициальную) табличку новых выполненных пунктов стандарта SQL/JSON:
- базовые SQL/JSON функции-конструкторы;
- SQL/JSON: JSON_OBJECTAGG;
- SQL/JSON: JSON_ARRAYAGG с ORDER BY;
- двоеточие в JSON_OBJECT или JSON_OBJECTAGG;
- базовые операторы запросов SQL/JSON;
- SQL/JSON: предикат IS JSON WITH UNIQUE KEYS;
- SQL/JSON: предложение с PASSING;
- JSON_TABLE: предложение с указанным PLAN;
- SQL/JSON: предложения ON EMPTY и ON ERROR;
- предложения с ON ERROR или ON EMPTY;
- JSON_TABLE: предложения NESTED COLUMNS одного уровня (siblings);
- JSON_QUERY;
- JSON_QUERY: опции в функциях с массивами;
- WITH UNIQUE KEYS в функциях-конструкторах SQL/JSON;
- JSON_TABLE: PLAN DEFAULT.
MERGE
До появления MERGE INTO при миграции с Oracle в Postgres его приходилось имитировать. Например, в статье Из Oracle да в Postgres есть пример с RETURNING и WITH.
В 2015, в PostgreSQL 10, появился INSERT … ON CONFLICT DO …. (PostgreSQL 9.5: что нового? Часть 1. INSERT… ON CONFLICT DO NOTHING/UPDATE и ROW LEVEL SECURITY).
В 2018 в PoostgreSQL 11 чуть не попал MERGE. Но из-за проблем патч откатили обратно. Это была настоящая драма, почти детективная история, и мы об этом писали в Битва при MERGE. Хроника с выводами и моралью. Бился за MERGE глава компании 2ndQuadrant (теперь в EDB) Саймон Риггс (Simon Riggs), он с настойчиво и изобретательно, всеми силами пытался протащить в версию патч, реализующий синтаксис команды MERGE.
И вот — свершилось! Документация прилагается.
Только что стало известно, что некоторые поправки всё же требуются. Они почти готовы.
JS
JS не входит в 4 процедурных языка PostgreSQL «из коробки» (эти четыре — PL/pgSQL, PL/Perl, PL/Python и PL/Tcl), но он распространён довольно широко. Особенно его реализация PL/V8 — на движке V8, за которым стоит Google.
PLV8 3.1.2: PLV8 — A Procedural Language in Javascript powered by V8
На сегодня это самая свежая версия языка. Вот документация.
PostgreSQL: Серверное программирование на «человеческом» языке (PL/Perl, PL/Python, PL/V8)
Статья Ивана Панченко не только о PL/V8. Много полезных примеров. И интересно сравнение возможностей и особенностей этих постгресовых процедурных языков. Сравниваются PL/pgSQL, PL/Perl, PL/Python — то есть в статье PL/V8 как бы занял в четвёрке место «коробочного» PL/Tcl, который сейчас не так актуален.
Postgres.js 3.0.5
Postgres.js — полноценный клиент Postgres, работающий с рантайм-библиотеками Node.js (на безе V8) и Deno (на базе V8, написана на Rust). Эта версия умеет изменять подписку в реальном времени, динамически строить запросы, работать с большими объектами, строить HA-архитектуры с возможностью соединения с разными URL, асинхронные курсоры и многое другое.
Quickgres 0.4.2
Другая клиентская библиотека, компактная и удобная, без внешних зависимостей. Работает непосредственно с протоколом PostgreSQL. Может исполнять запросы масштаба до 2ГБ, что может быть удобно для работы с видео в столбцах TOAST. Умеет прерывать долгоиграющие запросы, работать с двоичными параметрами и двоичными результатами запроса и многое другое.
Directus v9.8.0
Среда на базе Node.js, может работать как фронтэнд к Postgres, SQLite, MySQL, Oracle и другим SQL-базам. Создаёт слой, который оборачивает обращения к базе в API Real-Time GraphQL и Rest. В новой версии добавлена некоторая функциональность. Например, валидация на уровне полей.
PGX 0.4.0
pgx — среда разработки расширений PostgreSQL в Rust. В этой версии есть некоторые изменения, например, появилась поддержка компиляции для систем на базе Musl, таких как Alpine Linux.
ZomboDB
Это расширение PostgreSQL, которое интегрирует в него Elasticsearch. При этом никакого кода, специфичного для Elasticsearch там нет, только PostgreSQL API.
Ещё статьи и релизы
No UPDATE updates
Статью с таким интересным названием написал Александр Никитин из Data Egret. Он исследует происходящее с данными, используя hexedit. Вручную меняет данные на диске, смотрит, когда запрос к базе забирает данные из кэша, а когда с диска. Очень увлекательно.
Fuzzy Name Matching in Postgres
В своей статье Пол Рэмзи Paul Ramsy, Crunchy Data) рассказывает о расширении fuzzystrmatch, которое используется для определения Левенштейновского расстояния и умеет работать с алгоритмом Soundex. Для генерации случайных данных Пол использует генератор фальшивых имён.
Tidying Up With VACUUM
На пятницах (PG Phriday) в EDB появилась статья Шона Томаса (Shaun Thomas), где описаны все многочисленные параметры, влияющие на интенсивность вакуума.
Database Lab Engine 3.1: pgBackRest, timezones for CLI, DLE community
Новое:
- нативная поддержка pgBackRest как инструмента восстановления данных из архива (поддержка WAL-G уже есть);
- можно конфигурировать timezone в командной строке при использовании DLE в CI/CD-конвейерах.
Появились первые «внешние» статьи о работе с DLE: TESTING WITH PRODUCTION DATA MADE EASY
Питер Винкен (Pieter Vincken) пишет о Database Lab Engine в блоге JWorks — бельгийской компании Java-разработчиков.
Ну и от создателей — статья Николая Самохвалова: DLE 3.1: pgBackRest, timezones for CLI, DLE community.
Orafce 3.21.0
В этой версии оракловые regexp-функции
regexp_instr и regexp_replace были с некоторыми модификациями портированы (backported) из PostgreSQL 15. Использовать с осторожностью, так как отличия есть, в том числе на уровне поведения функций. Например, при обработке NULL.usql 0.10.0
usql — универсальный инструмент командной строки для различных СУБД: не только реляционных, но и NoSQL. Напоминает psql, но больше возможностей.
PgBouncer 1.17.0
Из новой функциональности: определения базы данных могут быть заданы в виде списка хостов. Соединение с хостами будет производиться по кругу (round-robin). При попытке соединения с несуществующей базой теперь генерится ошибка. Это сделано для того, чтобы клиенты, прошедшие аутентификацию не могли прощупывать, какие базы данных существуют. А ещё — увеличили максимальную длину пароля. Об этих и других изменениях — в Change log.
pgBadger v11.8
В этой версии популярной утилиты Жиля Дароля (Gilles Darold) только исправления, но список их не короткий. Кстати, появилась такая статья: pgbadger incremental mode via SSH
pg_plan_guarantee
Это расширение гарантирует, что планы не изменятся. Есть несколько демо-примеров.
Pgfe 2.0.0
Pgfe расшифровывается как PostGres FrontEnd. Написанного на C++. Предыдущая версия была альфа.
Образование
Курс DBA1, обновленный до версии PostgreSQL-13, уже на сайте. Официальное премьерное чтение состоится в Элисте на этой неделе.
For Newbies
PostGIS For Newbies
Как установить. Простенькие запросы с функциями PostGIS. Чем отличается geometry от geography. QGIS. shp2pgsql и ogr2ogr. Автор — Элизабет Кристенсен (Elizabeth Christensen) из Crunchy Data.
Есть там и ещё статьи Элизабет для «новеньких»:
Postgres Indexes for Newbies
По простенькому примеру и в нескольких фразах по каждому из основных индексов: B-tree, BRIN, GIST (в статье так, но обычно пишут GiST) и GIN. Примеры с уклоном в PostGIS. Про хэш, SP-GiST и bloom не рассказывается (и напоминаем, что в Postgres Pro Enterprise есть ещё и rum; и напоминаем об углублённом варианте — серии статей Егора Рогова об индексах).
Postgres Constraints for Newbies
О FOREIGN KEY, каскадных UPDATE и DELETE, о NOT NULL, CHECK, EXCLUDE.
Напомним, что в жанре for newbies есть и наша «малютка», доступная в PDF. А для серьёзного погружения есть PostgreSQL изнутри (она же в PDF) Егора Рогова.