Много ли нового в Чёртовой Дюжине?

?v=1

Речь пойдёт всего лишь о PostgreSQL 13. 8 апреля состоялась «заморозка» — PostgreSQL feature freeze, теперь в эту версию войдут только те фичи, которые приняты до этой даты.
Революционной эту версию, пожалуй, трудно назвать. Кардинальных, концептуальных изменений в ней нет. К тому же не успели войти в неё такие важные патчи, как Table и Functions для стандарта JSON/SQL, которых хотелось видеть еще в PG12 рядом с патчем JSONPath; не появились готовые встраиваемые хранилища — лишь дорабатывается интерфейс. Но список доработок всё же впечатляет. Мы подготовили довольно полную сводку вошедших в Чёртову Дюжину патчей.


Изменения в командах SQL


CREATE DATABASE… LOCALE
Утилиты initdb, createdb и команда CREATE COLLATION имеют параметр LOCALE, позволяющий сразу указать значения для LC_CTYPE и LC_COLLATE. Теперь такая же возможность появилась в команде CREATE DATABASE:

CREATE DATABASE db_koi8r TEMPLATE template0 
    ENCODING 'KOI8R' LOCALE 'ru_RU.KOI8R';


ALTER VIEW… RENAME COLUMN
Имя столбца в представлении теперь можно изменить командой ALTER VIEW. Раньше для этого требовалось пересоздавать представление.
Предположим, что столбцу забыли задать имя:

CREATE VIEW uptime AS SELECT current_timestamp, current_timestamp - pg_postmaster_start_time();
SELECT * FROM uptime;
       current_timestamp       |    ?column?     
-------------------------------+-----------------
 2020-03-23 15:37:00.088824+03 | 04:18:24.897856


Это можно исправить:

ALTER VIEW uptime RENAME COLUMN "?column?" TO uptime;
SELECT * FROM uptime;
       current_timestamp       |     uptime      
-------------------------------+-----------------
 2020-03-23 15:37:40.726516+03 | 04:19:05.535548

ALTER TABLE… ALTER COLUMN… DROP EXPRESSION
Генерируемый столбец таблицы теперь можно сделать обычным, то есть удалить выражение для его вычисления:

CREATE TABLE payments (
    id integer PRIMARY KEY,
    amount numeric(18,2),
    income_tax numeric(18,2) GENERATED ALWAYS AS (amount*0.13) STORED
);
INSERT INTO payments(id, amount) VALUES(1, 42);
\d payments
                                     Table "public.payments"
   Column   |     Type      | Collation | Nullable |                   Default            
        
------------+---------------+-----------+----------+--------------------------------------
 id         | integer       |           | not null |
 amount     | numeric(18,2) |           |          |
 income_tax | numeric(18,2) |           |          | generated always as ((amount * 0.13))
 stored
Indexes:
    "payments_pkey" PRIMARY KEY, btree (id)


Впоследствии решили, что следует явно задавать income_tax. Удаляем выражение:

ALTER TABLE payments ALTER COLUMN income_tax DROP EXPRESSION;
\d payments
                   Table "public.payments"
   Column   |     Type      | Collation | Nullable | Default
------------+---------------+-----------+----------+---------
 id         | integer       |           | not null |
 amount     | numeric(18,2) |           |          |
 income_tax | numeric(18,2) |           |          |
Indexes:
    "payments_pkey" PRIMARY KEY, btree (id)


Разумеется существующие данные из столбца никуда не делись:

SELECT * FROM payments;
 id | amount | income_tax
----+--------+------------
  1 |  42.00 |       5.46

DROP DATABASE… FORCE
Если требуется удалить базу данных, не дожидаясь отключения всех пользователей, то можно воспользоваться новой опцией FORCE команды DROP DATABASE.

CREATE DATABASE db;


Подключимся к новой базе данных:

CREATE EXTENSION IF NOT EXISTS dblink;
SELECT dblink_connect('dbname=db');
 dblink_connect
----------------
 OK


А теперь удалим, принудительно прерывая, как и pg_terminate_backend, открытые подключения:

DROP DATABASE db WITH (FORCE);

ALTER TYPE… SET STORAGE
Команда ALTER TYPE позволяет для базовых типов данных изменять различные свойства, в частности стратегию хранения. Раньше её можно было задавать только в команде CREATE TYPE.
Для демонстрации не будем создавать новый базовый тип, а воспользуемся существующим — tsquery. Но в начале создадим отдельную базу данных и подключимся к ней:

CREATE DATABASE db;
\c db


Для типа данных tsquery используется стратегия хранения plain, поэтому столбцы таблиц такого типа получают эту же стратегию:

SELECT typname,typstorage FROM pg_type WHERE typname = 'tsquery';
 typname | typstorage
---------+------------
 tsquery | p
CREATE TABLE queries (query tsquery);
SELECT attname, attstorage FROM pg_attribute WHERE attrelid = 'queries'::regclass AND attname = 'query';
 attname | attstorage
---------+------------
 query   | p


Если для новых таблиц требуется использовать другую стратегию, то можно изменить базовый тип:

ALTER TYPE tsquery SET (storage=external);
SELECT typname,typstorage FROM pg_type WHERE typname = 'tsquery';
 typname | typstorage
---------+------------
 tsquery | e


Изменится и тип хранения в новых таблицах:

CREATE TABLE new_queries (query tsquery);
SELECT attname, attstorage FROM pg_attribute WHERE attrelid = 'new_queries'::regclass AND attname = 'query';
 attname | attstorage
---------+------------
 query   | e


Нужно иметь в виду, что изменить стратегию, предполагающую использование TOAST, обратно на plain нельзя:

ALTER TYPE tsquery SET (storage=plain);
ERROR:  cannot change type's storage to PLAIN


Поэтому эксперименты проводились в отдельной БД, которую не жалко удалить.

ALTER STATISTICS… SET STATISTICS
Командой CREATE STATISTICS можно собирать списки самых частных значений для выбранных комбинаций столбцов таблицы. Количество собираемых самых частных значений определяется параметром default_statistics_target. Значение для конкретной статистики теперь можно изменить командой:

ALTER STATISTICS имя SET STATISTICS новое_значение;

FETCH FIRST с опцией WITH TIES
Как известно, в команде SELECT вместо указания LIMIT можно пользоваться синтаксисом, определенным в стандарте SQL:

SELECT *
FROM (VALUES (1,'1.1'), (2,'2.1'), (2,'2.2'), (3,'3.1')) t(a,b)
ORDER BY a
FETCH FIRST 2 ROWS ONLY;
 a |  b  
---+-----
 1 | 1.1
 2 | 2.1
(2 rows)


Теперь FETCH поддерживает и фразу WITH TIES, которая добавляет к выводу все «родственные» строки (строки, равные уже выбранным, если учитывать только условие сортировки):

SELECT *
FROM (VALUES (1,'1.1'), (2,'2.1'), (2,'2.2'), (3,'3.1')) t(a,b)
ORDER BY a
FETCH FIRST 2 ROWS WITH TIES;
 a |  b  
---+-----
 1 | 1.1
 2 | 2.1
 2 | 2.2
(3 rows)

Встроенные функции и типы данных


get_random_uuid
Новая функция get_random_uuid возвращает значение UUID версии 4(случайное значение):

SELECT gen_random_uuid();
           gen_random_uuid            
--------------------------------------
 25e02793-80c0-438c-be07-c94b966c43ab


Функция полезна для генерации уникальных значений типа UUID в распределенных системах.
Раньше надо было пользоваться библиотеками uuid-ossp или pgcrypto.

min_scale и trim_scale для значений типа numeric
Функция min_scale определяет количество значащих цифр в дробной части числа, а функция trim_scale отбрасывает незначащие нули:

SELECT n, min_scale(n), trim_scale(n) FROM (VALUES(42.42000)) as t(n);
    n     | min_scale | trim_scale
----------+-----------+------------
 42.42000 |         2 |      42.42

gcd и lcm
Пополнение в разделе математических функций. Теперь можно быстро найти наибольший общий делитель (gcm) и наименьшее общее кратное (lcm):

SELECT gcd(54,24), lcm(54,24);
 gcd | lcm
-----+-----
   6 | 216

Агрегатные функции min и max для типа pg_lsn
Для типа данных pg_lsn добавлены агрегатные функции min и max, что позволяет выполнять запросы вида:

SELECT min(restart_lsn) FROM pg_replication_slots;
SELECT min(sent_lsn) FROM pg_stat_replication;

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

CREATE TYPE currency AS (
    amount numeric(18,2),
    code   text
);
CREATE FUNCTION income_tax(quantity currency) RETURNS currency
    AS 'SELECT quantity.amount * 0.13, quantity.code' LANGUAGE SQL;


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

SELECT amount, code, amount::numeric(18,2)
FROM income_tax(ROW(42.42, 'руб'))\gx
-[ RECORD 1 ]--
amount | 5.5146
code   | руб
amount | 5.5146


В 13 версии результат корректный:

SELECT amount, code, amount::numeric(18,2)
FROM income_tax(ROW(42.42, 'руб'))\gx
-[ RECORD 1 ]
amount | 5.51
code   | руб
amount | 5.51

Локализованные названия в to_date () и to_timestamp ()
Функции to_date и to_timestamp научились понимать локализованные имена месяцев и дней недели. Раньше можно было использовать только английские названия:

SELECT to_date('Вторник, 24 Марта 2020', 'TMDay, DD TMMonth YYYY');
  to_date   
------------
 2020-03-24

normalize и IS NORMALIZED
Для соответствия стандарту SQL добавлена функция normalize (), нормализующая Unicode-строку, и предикат IS NORMALIZED, проверяющий, нормализована ли строка.

SELECT str, normalize(str, NFC) AS nfc,
       str IS NFC NORMALIZED AS is_nfc_normalized,
       normalize(str, NFKC) AS nfkc,
       str IS NFKC NORMALIZED AS is_nfkc_normalized
FROM (VALUES(U&'\0032\2075')) AS vals(str)\gx
-[ RECORD 1 ]------+---
str                | 2⁵
nfc                | 2⁵
is_nfc_normalized  | t
nfkc               | 25
is_nfkc_normalized | f


Подробнее о формах нормализации UNICODE.

Тип xid8 и функция xid8_current () для 64-битных номеров транзакций
Добавлен новый тип данных xid8 для 64-битного номера транзакции. Но нет, это не значит, что PostgreSQL перешел на 64-битные транзакции: все работает в точности как раньше. Но некоторые функции возвращают новый тип, например, теперь рекомендуется использовать pg_current_xact_id вместо старой функции txid_current, которая возвращала int8, и т. п.

Новые полиморфные типы данных семейства anycompatible
Добавили типы anycompatible, anycompatiblearray, anycompatiblenonarray, anycompatiblerange. В отличие от типов семейства anyelement, новые типы позволяют использовать не строго одинаковые, а совместимые фактически типы.
В следующем примере в функцию maximum в качестве аргументов, определенных как anycompatible, передаются integer и numeric. Возвращаемое значение приводится к общему для этих двух типов:

CREATE FUNCTION maximum(a anycompatible, b anycompatible) RETURNS anycompatible
	AS 'SELECT CASE WHEN a > b THEN a ELSE b END' LANGUAGE SQL;

SELECT f, pg_typeof(f) FROM maximum(42, 42.42) f;
   f   | pg_typeof
-------+-----------
 42.42 | numeric


Более того, типы anycompatible- и any- — это два независимых набора типов:

CREATE FUNCTION first_elems(a anyarray, b anycompatiblearray)
    RETURNS TABLE(a anyelement, b anycompatible) AS $$
        SELECT ROW(a[array_lower(a,1)], b[array_lower(b,1)]);
    $$ LANGUAGE sql;
SELECT first_elems(ARRAY[1,2,3], ARRAY['a','b','c']) AS str, 
       first_elems(ARRAY[1.1,2.2,3.3],ARRAY[true,false]) bool;
  str  |  bool   
-------+---------
 (1,a) | (1.1,t)

Процедурные языки


Transform для типа bool в PL/Perl
Совсем недавно был закоммичен TRANSFORM Ивана Панченко (заместитель генерального директора Postgres Professional) — bool_plperl. Postgres передает в PL/Perl логические значения как t или ft, но для Perl f не значение ложь логической переменной, а просто буква f. Эту проблему можно решить разными способами (см. переписку), но создание TRANSFORM для bool — самый практичный.

Быстрое исполнение простых выражений в PL/pgSQL
Простые выражения (как минимум не содержащие обращения к таблицам и не требующие блокировок) будут выполняться быстрее. Раньше в этих случаях время непроизводительно тратилось на обращения к планировщику на каждом цикле.

CREATE OR REPLACE FUNCTION slow_pi() RETURNS double precision AS $$
    DECLARE
        a double precision := 1;
        s double precision := 1;
        r double precision := 0;
    BEGIN
        FOR i IN 1 .. 10000000 LOOP
            r := r + s/a; a := a + 2; s := -s;
        END LOOP;
        RETURN 4*r;
    END;
    $$ LANGUAGE plpgsql;


Вызываем slow_pi () в PG12:

SELECT slow_pi();
          slow_pi      
    --------------------
     3.1415925535898497
    (1 row)
    Time: 13060,650 ms (00:13,061)


Теперь в PG13:

SELECT slow_pi();
          slow_pi      
    --------------------
     3.1415925535897915
    (1 row)
    Time: 2108,464 ms (00:02,108)

Доверенные расширения вместо pg_pltemplate
В системном каталоге стало меньше на одну таблицу. Удалили pg_pltemplate. В ней хранились свойства процедурных языков, которые нужны при выполнении CREATE LANGUAGE. Теперь решили в скрипты расширений соответствующих языков прописать свойства из pg_pltemplate, а от самой таблицы избавиться. Но для реализации задуманного нужно предусмотреть возможность владельцу базы данных (без прав суперпользователя) создать доверенный язык из скрипта расширения. Ведь сейчас для создания, к примеру plpgsql владелец базы данных не обязан быть суперпользователем.

Поступили следующим образом. В управляющем файле расширений появился новый логический параметр — trusted. Если оба параметра trusted и superuser включены, то создать расширение может не только суперпользователь, но и пользователь с правом CREATE на текущую базу данных (а значит и её владелец). При выполнении скрипта такого расширения будут использоваться права суперпользователя, инициализировавшего кластер. Ему же будут принадлежать созданные расширением объекты, хотя владельцем самого расширения будет создающий пользователь.

Важные следствия этих изменений:

  • Доверенные расширения открывают дорогу сторонним разработчикам к созданию других доверенных языков. Сейчас мы ограничены лишь plpgsql, plperl и pltcl.
  • В pg_pltemplate было жестко прописано, что plpython относится ко второй версии языка. Отказ от pg_pltemplate это шаг (необходимый, хотя и недостаточный) к переходу на python 3.

Индексы


Сжатие B-tree
Важный и долгожданный (работа началась аж в 2015-м) патч написанный Анастасией Лубенниковой (Postgres Professional) и Питером Гейганом (Peter Geoghegan) закоммичен, наконец, Питером. Об этом Настя успела рассказать на PGconf India. Postgres научился значительно сокращать размеры индексов B-tree за счет дедупликации, то есть экономии на повторяющихся ключах индекса. Эти индексы были серьезно переработаны для того, чтобы сжатие стало возможно без потерь в совместимости с предыдущими версиями индексов. Идея дедупликации взята из более гибкой архитектуры индексов типа GIN (обратные индексы — Generalized Inverted Index).

В этих индексах чаще, чем в B-tree, встречается ситуация, когда ключу соответствует большое количество записей. В случае обработки текстов, например, одна и та же лексема обычно встречается в нескольких документах. И она хранится в индексе только один раз. Этого индексы B-tree до последнего времени не умели.

Индексы B-tree отличаются от индексов GIN прежде всего страницами «листьев». В зависимости от количества записей, относящихся к одному и тому же значению ключа, возможны варианты: страница содержит только posting list — список TID-ов (идентификаторов индексируемых записей), если список мал, а если TID-ов много, то вместо списка значений хранятся новые «ветви деревьев» — ссылки на другие страницы типа posting list или другие ветви дерева (они называются posting tree).

Такая древовидная структура похожа B-tree, но отличается существенными деталями: например списки для перемещения по страницам одного уровня дерева в GIN однонаправленные, а не двунаправленные. Поэтому (в том числе) хорошей совместимости новых, дедуплицированных индексов со старыми версиями добиться непросто. И доработки действительно заняли больше 3-х лет. Надо было также отработать механизм очистки (микровакуум) и другие нюансы.

На тестах производительности все индексы, к которым дедупликация применима, сжались примерно в 3 раза. Сжатие дубликатов помогает и уникальным индексам, устраняя проблему распухания индекса при большом темпе изменений таблицы. Новое поведение можно будет подключать и отключать на уровне настройки индексов.

Полное сканирование по индексу GIN не делается там, где не нужно
Этот патч позволяет в некоторых случаях избежать полного прохода по всему GIN-индексу. Некоторые операции, хоть и поддерживаются GIN-индексом, выполняются полным просмотром индекса. Возьмем, например, индекс для полнотекстового поиска по столбцу tsvector. Если поисковый запрос имеет вид «что угодно, кроме заданного слова», то весь индекс придется прочитать целиком. Если же при этом в запросе присутствует и другое условие, которое не требуют полного просмотра индекса, то индекс все равно будет сканироваться полностью.

С новой оптимизацией сначала будет использовано более точное условие, позволяющее получить от индекса выигрыш, а затем полученные результаты будут перепроверены, чтобы учесть и другое ограничение. Сравните количество страниц, которые были прочитаны в версии 12 (Buffers):

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF)
SELECT * FROM mail_messages WHERE tsv @@ to_tsquery('!vacuum') AND tsv @@ to_tsquery('analyze');
                                             QUERY PLAN                                             
------------------------------------------------------------------
Bitmap Heap Scan on mail_messages (actual rows=5864 loops=1)
   Recheck Cond: ((tsv @@ to_tsquery('!vacuum'::text)) AND (tsv @@ to_tsquery('analyze'::text)))
   Heap Blocks: exact=5167
   Buffers: shared hit=24 read=27405
   ->  Bitmap Index Scan on mail_messages_tsv_idx (actual rows=5864 loops=1)
         Index Cond: ((tsv @@ to_tsquery('!vacuum'::text)) AND (tsv @@ to_tsquery('analyze'::text)))
         Buffers: shared hit=24 read=22238
 Planning Time: 0.283 ms
 Execution Time: 3258.234 ms


с количеством буферов в новой версии:

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF)
SELECT * FROM mail_messages WHERE tsv @@ to_tsquery('!vacuum') AND tsv @@ to_tsquery('analyze');
                                             QUERY PLAN                                             
---------------------------------------------------------------------------
Bitmap Heap Scan on mail_messages (actual rows=5864 loops=1)
   Recheck Cond: ((tsv @@ to_tsquery('!vacuum'::text)) AND (tsv @@ to_tsquery('analyze'::text)))
   Heap Blocks: exact=5156
   Buffers: shared hit=5179
   ->  Bitmap Index Scan on mail_messages_tsv_idx (actual rows=5864 loops=1)
         Index Cond: ((tsv @@ to_tsquery('!vacuum'::text)) AND (tsv @@ to_tsquery('analyze'::text)))
         Buffers: shared hit=23
 Planning Time: 0.250 ms
 Execution Time: 8.779 ms

С аналогичной ситуацией можно столкнуться и при использовании триграмм, и при проверке на вхождение массивов.

Параметры классов операторов
В PostgreSQL многие индексные методы доступа представляют собой «каркас», который берет на себя высокоуровневую реализацию алгоритма поиска, работу со страницами и блокировками, журналом WAL. А привязка к конкретным типам данных и операторам выполняется с помощью классов операторов.
До сих пор классы операторов не могли иметь параметров. Например, для полнотекстового поиска может применяться GiST-индекс с классом операторов tsvector_ops (о классах операторов GiST здесь). Этот класс операторов использует сигнатурное дерево, а длина сигнатуры была фиксирована (124 байта). Теперь длину можно указать явно, что позволяет управлять балансом между размером индекса и эффективностью (числом хеш-коллизий):

CREATE INDEX ON mail_messages USING gist(tsv tsvector_ops(siglen=1024));


Аналогичные изменения для начала сделаны и для других классов операторов GiST, в которых используется сигнатурное дерево, что относится к расширениям hstore, intarray, ltree и pg_trgm.
Но главная идея, ради которой затевалось это изменение, — возможность передать JSONPath-выражение в GIN-индекс, чтобы индексировать не весь JSON-документ, а только нужную его часть. Во многих случаях это позволит радикально сократить размеры индексов. Но эта работа еще предстоит.
Идея Олега Бартунова, реализация Никиты Глухова и Александра Короткова (все трое Postgres Professional).

Добавлен оператор <-> (box, point)
Добавили недостающую операцию для использования в kNN для GiST и SP-GiST. В PG12 при работе с геометрическими типами point и box можно использовать оператор расстояния <->(point, box), и он будет ускорять поиск с индексами GiST and SP-GiST. Но симметричный ему оператор <->(box, point) не был реализован, хотя box уже понимал расстояния до более сложных типов — многоугольников и кругов.

CREATE TABLE points(point point);
CREATE TABLE boxes(box box);
INSERT INTO points VALUES('1,2','3,4');
INSERT INTO boxes VALUES(box(point(3,4),point(5,6)), box(point(13,14),point(15,16)));


В PG12:

SELECT p.point, b.box, b.box  <-> p.point distance FROM points AS p, boxes AS b ORDER BY distance;
ОШИБКА:  оператор не существует: box <-> point


Если наоборот, то всё ок:

SELECT p.point, b.box, p.point  <-> b.box distance FROM points AS p, boxes AS b ORDER BY distance;
 point |       box       |      distance
-------+-----------------+--------------------
 (1,2) | (5,6),(3,4)     | 2.8284271247461903
 (2,1) | (5,6),(3,4)     | 3.1622776601683795
 (1,2) | (15,16),(13,14) | 16.970562748477143
 (2,1) | (15,16),(13,14) | 17.029386365926403


А в PG13:

SELECT p.point, b.box, b.box  <-> p.point distance FROM points AS p, boxes AS b ORDER BY distance;
 point |       box       |      distance
-------+-----------------+--------------------
 (1,2) | (5,6),(3,4)     | 2.8284271247461903
 (2,1) | (5,6),(3,4)     | 3.1622776601683795
 (1,2) | (15,16),(13,14) | 16.970562748477143
 (2,1) | (15,16),(13,14) | 17.029386365926403


Индексы GiST и SP-GiST будут ускоряться и на этой операции.
Обратите внимание, что в PG13, если спросить:

SELECT count(*) FROM pg_operator WHERE oprname = '<->';
 count 
-------
    28


а если проделать то же в PG12, получим 20 записей: в 13-й версии список пополнился аж 8 операторами.

JSON


Поддержка метода .datetime () для jsonpath
Это один из непрошедших патчей большой серии патчей JSONPath, который не успели доделать к выходу PG12. Часть стандарта JSON/SQL. Проблема была в том, что все функции серии патчей JSONPath являются immutable, но сравнение дат учитывает текущий часовой пояс, который может меняться во время сессии.
В таких случаях мы разрешаем существующим immutable-функциям выбрасывать ошибку про non-immutable сравнениях. В то же время в этом патче есть функции с суффиксом _tz, которые работают стабильно в операциях с timezone.

Новая функция — jsonb_set_lax function
Вообще lax это нестрогий (в отличие от strict) режим работы функций с jsonb. В данном случае эта функция будет работоспособна в ситуации, когда один из аргументов, которые она принимает, равен NULL. В отличие от строгой версии — jsonb_set () — у нее есть дополнительный аргумент, который указывает на действия в случае NULL. Варианты: use_json_null / raise_exception / return_target / delete_key. Варианты предложены заинтересованными пользователями.

Оптимизированы некоторые jsonb-функции
Оптимизировано очень много, главным образом усилиями Никиты Глухова (Postgres Professional). Но разбирать каждый пункт в данном случае бессмысленно: во-первых, изобилие их раздует и так не короткую статью;, а во-вторых, изменения касаются внутреннего устройства, и не всякому пользователю это интересно. Поэтому лишь перечислим большинство из них:

  1. Оптимизирована функция JsonbExtractScalar ();
  2. Оптимизирован оператор #>>, функции jsonb_each_text (), jsonb_array_elements_text ();
  3. Оптимизировано распознавание типа JsonbContainer в get_jsonb_path_all ();
  4. Извлечение (fetch) первого токена из итератора JsonbIterator заменена на легкий макрос JsonbContainerIsXxx ();
  5. Более удобное извлечение ключа — findJsonbKeyInObject ();
  6. Оптимизировано хранение результата findJsonbValueFromContainer () и getIthJsonbValueFromContainer ();
  7. Оптимизирована функция get_jsonb_path_all (), она больше не использует итератор;
    Переписан код JsonbValueAsText.

Как говорится, здесь могли бы находиться сообщения о патчах SQL/JSON: JSON_TABLE и SQL/JSON: functions. Мы ждали и надеялись их увидеть закоммиченными. Увы, этого не произошло. Патчи большие и затрагивают много файлов. Надеемся на попадание в PG14. А пока JSONPath остается в одиночестве.

Утилиты и расширения


pgbench


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

Кроме того, добавили команду \aset, похожую на \gset, но позволяющую устанавливать значения переменным нескольких запросов, отправленных за один раз. Следующая строка, отправленная на сервер для исполнения, установит обе переменные one и two:

SELECT 1 AS one \; SELECT 2 AS two \aset

pg_dump


pg_dump научился выгружать данные из сторонних таблиц. Через параметр --include-foreign-data можно задать список сторонних серверов, данные из таблиц которых будут выгружены.

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

psql


Серия небольших патчей делает работу в psql более комфортной:

  • Улучшено автодополнение по табуляции для нескольких команд.
  • В дополнение к \echo, отправляющей строку в STDOUT, новая команда \warn отправляет строку в стандартный вывод ошибок (STDERR).
  • Команда \d для toast-таблицы показывает информацию об индексе и основной таблице. А для индекса по секционированной таблице командой \d+ можно получить список индексных секций с табличными пространствами.
  • В команде \dt+ новый столбец «Persistence» показывает являются ли таблицы нежурналируемыми (unlogged) или обычными (permanent).
  • Отредактированный командой \e запрос можно вывести на экран, если в конце запроса не ставить ; (или команды \g*). Сейчас, после возвращения из редактора в окно psql нельзя увидеть текст команды до выполнения.
  • Подсказка по умолчанию будет включать информацию о статусе текущей транзакции. Для этого в переменные PROMPT1 и PROMPT2 добавлен спецсимвол %x.
  • Новые команды для получения дополнительной информации о методах доступа: \dAc, \dAf, \dAo, \dAp
  • В \g теперь можно указать в скобках любые опции, которые поддерживает \pset. Они будут действовать только на текущую команду.

libpq


Небольшие изменения в части подключения к PostgreSQL:

  • Устранена неточность в описании параметров host и hostadr и связанная с этим несогласованность вывода команды \conninfo утилиты psql.
  • Если ключ клиентского сертификата хранится в зашифрованном виде, то ввести пароль можно только в интерактивном режиме. Новый параметр sslpassword позволит расшифровывать ключ неинтерактивно.
  • Два новых параметра sslminprotocolversion и sslmaxprotocolversion позволяют задать ограничение версии протокола SSL/TCL, с которой разрешается подключение.

reindexdb


Новый параметр --jobs утилиты reindexdb задает количество соединений к базе данных, в которых будут одновременно перестраиваться индексы.

pg_rewind


Ограничения утилиты понемногу снимаются, а возможности увеличиваются.
Во-первых, pg_rewind теперь может записывать информацию для восстановления (как это умеет делать pg_basebackup), а также запускать восстановление и последующую остановку экземпляра, если он не был остановлен через контрольную точку (раньше это надо было делать вручную).

А во-вторых, pg_rewind научилась работать с архивом WAL.
После того, как утилита находит точку расхождения WAL между двумя серверами, она должна построить список всех страниц, которые надо скопировать на целевой кластер, чтобы устранить различия. Для этого утилите требуются все WAL-файлы, начиная с найденной точки. Если необходимые WAL-файлы отсутствуют на целевом кластере, утилита раньше не могла выполнить свою работу.
С этим патчем Алексея Кондратова (Postgres Professional) pg_rewind сможет прочитать недостающие WAL-сегменты из архива журнальных файлов, используя параметр restore_command, если указан новый ключ -c или --restore-target-wal.

pg_waldump


pg_waldump будет расшифровывать запись о подготовленной транзакции.

amcheck


Расширение amcheck научилось лучше распознавать повреждения в индексах типа B-дерево.
Кстати, теперь сообщения в журнале сервера о поврежденных страницах будут различаться для индексов и таблиц.

pageinspect


Функция heap_tuple_infomask_flags расширения pageinspect расшифровывает значения полейinfomask и infomask2, возвращаемых функцией heap_page_items. Полезно при расследовании ситуаций, связанных с повреждением данных.

postgres_fdw


Суперпользователь на уровне сопоставления имен пользователей может разрешить обычным пользователям использовать подключение без пароля:

ALTER USER MAPPING FOR несуперпользователь SERVER сервер
    OPTIONS (ADD password_required 'false');

Это сделано в том числе и для того, чтобы в качестве параметров подключения можно было использовать sslkey и sslcert.

adminpack


В расширении adminpack новая функция — pg_file_sync. С её помощью можно делать fsync для файлов, записываемых сервером на диск, например через pg_file_write или COPY TO.

Мониторинг


pg_stat_slru


В разделяемой памяти сервера находится не только большой буферный кеш, но и некоторое количество других, более простых, кешей (например, для статуса транзакций). Для них используется простой алгоритм вытеснения наименее часто используемых страниц (simple least-recently-used, или SLRU). До сих пор такие кеши «просто работали», но назрела необходимость их мониторинга, в первую очередь для разработчиков ядра PostgreSQL, чтобы разобраться, нужно ли что-то в них менять. С этой и целью появилось новое представление pg_stat_slru.

pg_stat_activity


В представлении pg_stat_activity новый столбец — leader_id. Для процессов, участвующих в выполнении параллельных запросов, он заполняется номером ведущего процесса. А у ведущего процесса leader_id равен номеру процесса, pid.
Следующий запрос показывает какие запросы и какими процессами сейчас выполняются в параллельном режиме:

SELECT query, leader_pid, 
    array_agg(pid) filter(WHERE leader_pid != pid) AS members
  FROM pg_stat_activity
 WHERE leader_pid IS NOT NULL
 GROUP BY query, leader_pid;

Есть изменения в списке событий ожидания. Добавили два новых события: BackupWaitWalArchive и RecoveryPause. А двум другим дали более точные имена: RecoveryWalStream -> RecoveryRetrieveRetryInterval, RecoveryWalAll -> RecoveryWalStream.

И еще два новых события ожидания, происходящих на реплике: RecoveryConflictSnapshot (конфликт с VACUUM, удалившем нужные версии строк) и RecoveryConflictTablespace (конфликт, связанный с удалением табличного пространства).

pg_stat_statements


До сих пор, расширение pg_stat_statements рассматривало запросы с фразой FOR UPDATE и без этой фразы как один и тот же запрос. Теперь запросы с FOR UPDATE учитываются отдельно.

Увеличилось и количество собираемой информации. Отныне фиксируется не только информация о ресурсах на выполнение команд, но и статистика по генерируемым журнальным записям. Новые столбцы представления: wal_bytes — объем сгенерированных записей, wal_records — количество сгенерированных записей, wal_num_fpw — количество полных образов страниц (full page writes).

Это стало возможным благодаря подготовленной инфраструктуре для отслеживания использования WAL. Поэтому теперь и EXPLAIN с новой опцией WAL будет показывать объем генерируемых записей:

CREATE TABLE t (id int);
EXPLAIN (ANALYZE, WAL, COSTS OFF, TIMING OFF, SUMMARY OFF)
    INSERT INTO t VALUES(1);
              QUERY PLAN              
--------------------------------------
 Insert on t (actual rows=0 loops=1)
   WAL:  records=1  bytes=59
   ->  Result (actual rows=1 loops=1)

Расширение auto_explain, VACUUM с VERBOSE, и autovacuum тоже используют созданную инфраструктуру и будут выводить объемы WAL.

Возвращаясь к pg_stat_statements. Если включен новый параметр pg_stat_statements.track_planning, то для каждого оператора будет записываться дополнительная статистика, относящаяся к планировщику: количество построений плана; суммарное время планирования; минимальное и максимальное время одного планирования, а также среднее и стандартное отклонение.

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

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF) 
    SELECT * FROM pg_class;
                   QUERY PLAN                   
------------------------------------------------
 Seq Scan on pg_class (actual rows=386 loops=1)
   Buffers: shared hit=9 read=4
 Planning Time: 0.782 ms
   Buffers: shared hit=103 read=11
 Execution Time: 0.219 ms

Журнал


  • Параметр log_statement_sample_rate теперь определяет долю команд SQL, записываемых в журнал, если продолжительность их выполнения превышает log_min_duration_sample(новый параметр).
    Отметим, что значение параметра log_min_duration_statement имеет более высокий приоритет, т.е. если log_min_duration_statement меньше, чем log_min_duration_sample, то в журнал будут записываться все команды, невзирая на долю определенную в log_statement_sample_rate.
    Кроме того, новый параметр log_transaction_sample_rate позволяет указывать долю транзакций, все команды которых будут записаны в журнал, вне зависимости от времени их выполнения.
  • Вместе с командами, завершившимися ошибкой (что определяется параметром log_min_error_statement), в журнал теперь могут записываться и значения переменных привязки. Максимальный размер сохраняемых в журнале значений определяется новым параметром log_parameter_max_length_on_error. По умолчанию он равен 0, что отключает вывод.
    Включение log_parameter_max_length_on_error приводит к дополнительным накладным расходам на выполнение всех команд SQL, а не только ошибочных, но позволяет более точно диагностировать ошибки.
    Для не ошибочных операторов (которые выводятся в журнал параметрами log_statements и log_duration) также появился параметр, ограничивающий размер: log_parameter_max_length, По умолчанию значения записываются полностью, как и раньше.
  • В журнал сервера теперь можно записывать тип процесса (pg_stat_activity.backend_type). Для этого в параметре log_line_prefix предусмотрен спецсимвол %b. А если журнал пишется в формате csv (log_destination=csvlog), то столбец backend_type туда уже включен.

Ход выполнения


Новые представления pg_stat_progress_analyze и pg_stat_progress_basebackup позволяют отслеживать ход выполнения сбора статистики командой ANALYZE и создания резервной копии утилитой pg_basebackup соответственно.

Оптимизация


Вычисление на этапе планирования immutable-функций в предложении FROM
Патч Александра Кузьменкова и Александра Парфёнова (оба из Postgres Professional) помогает в случаях, когда в предложении FROM встречается вызов функции, фактически являющейся константой. В этом случае вместо того, чтобы выполнять соединение, значение константы подставляется в необходимые места запроса.
Вот как это происходит на примере запроса, связанного с полнотекстовым поиском:

EXPLAIN (COSTS OFF)
SELECT subject, ts_rank_cd(tsv, q) AS rank
FROM mail_messages, to_tsquery('english', 'tuple') q
WHERE tsv @@ q
ORDER BY rank DESC;
                              QUERY PLAN                              
------------------------------------------------------------------
 Sort
   Sort Key: (ts_rank_cd(mail_messages.tsv, '''tuple'''::tsquery)) DESC
   ->  Bitmap Heap Scan on mail_messages
         Recheck Cond: (tsv @@ '''tuple'''::tsquery)
         ->  Bitmap Index Scan on mail_messages_tsv_idx
               Index Cond: (tsv @@ '''tuple'''::tsquery)


Здесь нет соединения, а значение 'tuple':: tsquery подставлено в запрос уже на этапе планирования. В версии 12 была совсем другая картина:

EXPLAIN (COSTS OFF)
SELECT subject, ts_rank_cd(tsv, q) AS rank                            
FROM mail_messages, to_tsquery('english', 'tuple') q
WHERE tsv @@ q                            
ORDER BY rank DESC;
                          QUERY PLAN                         
-----------------------------------------------------
 Sort
   Sort Key: (ts_rank_cd(mail_messages.tsv, q.q)) DESC
   ->  Nested Loop
         ->  Function Scan on q
         ->  Bitmap Heap Scan on mail_messages
               Recheck Cond: (tsv @@ q.q)
               ->  Bitmap Index Scan on mail_messages_tsv_idx
                     Index Cond: (tsv @@ q.q)

Инкрементальная сортировка
В случаях, когда необходима сортировка по многим ключам (k1, k2, k3…), планировщик теперь может воспользоваться знанием о том, что данные уже отсортированы по нескольким из первых ключей (например, k1 и k2). В этом случае можно не пересортировывать все данные заново, а разделить их на последовательные группы с одинаковыми значениями k1 и k2, и «досортировать» по ключу k3.
Таким образом вся сортировка распадаетс

© Habrahabr.ru