Postgresso #3 (40)19.04.2022 23:46

e1fkhn5gp7hlso9v5ad1shcfhge.jpeg

ИТ-инфраструктура — это как водопровод, без неё жизнь уже почти невозможна. И в эти безрадостные дни мы продолжаем выпускать 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!

3jat0vtqqfu9_pgmngzwevmorco.jpeg
Скрин стандартов из статьи Олега Бартунова

Между этими двумя постами три года. В первом он обозначил 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-конвейерах.

Подробней о новшествах в Release notes.

Появились первые «внешние» статьи о работе с 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) Егора Рогова.

© Habrahabr.ru