Ноябрьский коммитфест принес немало интересного! Без лишних предисловий приступаем к обзору.

Самое интересное об июльском и сентябрьском коммитфестах ― в предыдущих статьях серии: 2023-07, 2023-09.

Триггер ON LOGIN
Триггеры событий для REINDEX
ALTER OPERATOR: commutator, negator, hashes, merges
pg_dump --filter=dump.txt
psql: отображение привилегий по умолчанию
pg_stat_statements: отслеживание времени появления оператора и сброс min/max статистики
pg_stat_checkpointer: статистика процесса контрольной точки
pg_stats: статистика столбцов диапазонных типов
Планировщик: исключение лишних соединений таблицы самой с собой
Планировщик: статистика материализованных CTE
Планировщик: доступ к таблице по нескольким условиям
Оптимизация просмотра индекса при поиске по диапазону
dblink, postgres_fdw: детализированные события ожидания
Логическая репликация: перенос слотов репликации при обновлении сервера публикации
Журналирование использования слотов репликации
Unicode: новые информационные функции
Новая функция xmltext
Поддержка AT LOCAL
Бесконечные интервалы
ALTER SYSTEM с неизвестными пользовательскими параметрами
Сборка сервера из исходных кодов

Триггер ON LOGIN
commit: e83d1b0c

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

Как обычно, триггер создается в два этапа. Сначала триггерная функция:

CREATE FUNCTION check_login() RETURNS event_trigger
AS $$
BEGIN
    IF session_user = 'postgres' THEN RETURN; END IF;

    IF to_char(current_date, 'DY') IN ('SAT','SUN')
    THEN
        RAISE 'Хороших выходных, увидимся в понедельник!';
    END IF;
END;
$$ LANGUAGE plpgsql;

Затем сам триггер:

CREATE EVENT TRIGGER check_login
    ON LOGIN
    EXECUTE FUNCTION check_login();

Теперь можно быть уверенными, что по выходным, к всеобщему удовольствию, пользователи не будут мешать администратору. ?

$ psql -U alice -d postgres

psql: error: connection to server on socket "/tmp/.s.PGSQL.5401" failed: FATAL:  Хороших выходных, увидимся в понедельник!
CONTEXT:  PL/pgSQL function check_login() line 6 at RAISE

См. также
Waiting for PostgreSQL 17 – Add support event triggers on authenticated login – select * from depesz;

Триггеры событий для REINDEX
commit: f21848de

Триггеры событий теперь срабатывают для команды REINDEX. Их можно определить для событий ddl_command_start и ddl_command_end.

ALTER OPERATOR: commutator, negator, hashes, merges
commit: 2b5154be

В команде ALTER OPERATOR теперь можно указать коммутирующий оператор (COMMUTATOR) и обратный для него (NEGATOR), если эти операторы не были указаны в CREATE OPERATOR. А также добавить поддержку соединений хешированием (HASHES) и соединений слиянием (MERGES).

pg_dump --filter=dump.txt
commit: a5cf808b

В новом параметре --filter утилиты pg_dump можно указать имя файла, в котором перечислены объекты для включения или исключения из выгрузки:

$ cat dump.txt

include table      bookings
exclude table_data bookings

$ pg_dump -d demo --filter=dump.txt |grep -v -E '^SET|^SELECT|^--|^$'

CREATE TABLE bookings.bookings (
    book_ref character(6) NOT NULL,
    book_date timestamp with time zone NOT NULL,
    total_amount numeric(10,2) NOT NULL
);
ALTER TABLE bookings.bookings OWNER TO postgres;
COMMENT ON TABLE bookings.bookings IS 'Bookings';
COMMENT ON COLUMN bookings.bookings.book_ref IS 'Booking number';
COMMENT ON COLUMN bookings.bookings.book_date IS 'Booking date';
COMMENT ON COLUMN bookings.bookings.total_amount IS 'Total booking cost';
ALTER TABLE ONLY bookings.bookings
    ADD CONSTRAINT bookings_pkey PRIMARY KEY (book_ref);

Подробности синтаксиса файла описаны в документации к утилите.

Параметр полезен, если перечень объектов базы данных настолько большой, что перечисление их в командной строке запуска pg_dump превышает допустимый размер команды ОС.

Параметр --filter также добавлен в pg_dumpall и pg_restore.

psql: отображение привилегий по умолчанию
commit: d1379ebf

Команды psql были неудобны для просмотра привилегий по умолчанию. Рассмотрим на примере схем, хотя сказанное далее относится к просмотру привилегий любых объектов.

CREATE SCHEMA s;

После создания схемы ее владелец по умолчанию имеет обе привилегии: USAGE и CREATE. Но команда \dn+ их не покажет, поскольку они не записаны в pg_namespace.nspacl, там сейчас NULL:

16=# SELECT nspacl IS NULL
FROM pg_namespace
WHERE oid = 's'::regnamespace;

 ?column?
----------
 t

16=# \pset null '(null)'
16=# \dn+ s

                  List of schemas
 Name |  Owner   | Access privileges | Description
------+----------+-------------------+-------------
 s    | postgres |                   |

Обратите внимание, что даже установка \pset null не повлияла на отображение NULL значений. Команды psql для просмотра объектов системного каталога игнорировали эту установку.

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

16=# GRANT USAGE ON SCHEMA s TO public;
16=# REVOKE USAGE ON SCHEMA s FROM public;

16=# \dn+ s

                   List of schemas
 Name |  Owner   |  Access privileges   | Description
------+----------+----------------------+-------------
 s    | postgres | postgres=UC/postgres |

Владелец может сам у себя отозвать привилегии (а потом выдать обратно):

16=# REVOKE ALL ON SCHEMA s FROM postgres;

Сейчас значение pg_namespace.nspacl представляет собой пустой массив типа aclitem[]. Пустой массив — это не NULL, но как это понять из вывода команды?

16=# \dn+ s

                  List of schemas
 Name |  Owner   | Access privileges | Description
------+----------+-------------------+-------------
 s    | postgres |                   |

Только запросом к pg_namespace.

Что изменилось в 17-й версии. Установка \pset null теперь влияет на значения NULL:

17=# CREATE SCHEMA s;

17=# \pset null '(null)'
17=# \dn+ s

                  List of schemas
 Name |  Owner   | Access privileges | Description
------+----------+-------------------+-------------
 s    | postgres | (null)            | (null)

А отсутствие привилегий (пустой массив) отображается специальным значением (none):

17=# REVOKE ALL ON SCHEMA s FROM postgres;
17=# \dn+ s

                  List of schemas
 Name |  Owner   | Access privileges | Description
------+----------+-------------------+-------------
 s    | postgres | (none)            | (null)

pg_stat_statements: отслеживание времени появления оператора и сброс min/max-статистики
commit: dc9f8a79

В pg_stat_statements новый столбец statssince, в котором фиксируется время начала сбора статистики каждого оператора. Также появилась возможность сбросить статистику столбцов min/max* для отдельных операторов вызовом pg_stat_stetments_reset с параметром minmax_only. Время сброса этой статистики фиксируется в столбце minmax_stats_since.

Изменения полезны для систем мониторинга, основанных на сэмплировании информации из pg_stat_statements. В частности, они позволят отказаться от сброса всей статистики перед созданием снимов.

pg_stat_checkpointer: статистика процесса контрольной точки
commit: 96f05261, 74604a37

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

Теперь количество столбцов в pg_stat_bgwriter заметно уменьшилось. Информацию о статистике процесса контрольной точки перенесли в новое представление pg_stat_checkpointer (первый коммит).

В тоже время столбцы buffers_backend и buffers_backend_fsync удалили из pg_stat_bgwriter, так как более точную и детальную информацию об обслуживающих процессах можно получить из появившегося в 16-й версии представления pg_stat_io (второй коммит).

pg_stats: статистика столбцов диапазонных типов
commit: bc3c8db8

Статистика для столбцов диапазонных типов давно собирается и хранится в таблице pg_statistic. Однако эта информация не отображалась в представлении pg_stats, что данный патч и исправляет.

В pg_stats добавлены столбцы range_length_histogram, range_empty_frac, range_bounds_histogram.

Планировщик: исключение лишних соединений таблицы самой с собой
commit: d3d55ce5

В плохо написанном запросе таблица может без всякой необходимости соединяться сама с собой, синтаксис SQL этого не запрещает. Авторство таких запросов обычно за различными ORM, а оптимизацией часто приходится заниматься тем, кто не может повлиять на формирование запроса.

В 17-й версии планировщик научился вычислять подобные излишние соединения и не включать их в план запроса. В следующем примере используется ненужное полусоединение таблицы bookings самой с собой.

17=# EXPLAIN (costs off)
WITH b AS (
    SELECT book_ref FROM bookings
)
SELECT *
FROM bookings
WHERE book_ref IN (SELECT book_ref FROM b);

            QUERY PLAN            
----------------------------------
 Seq Scan on bookings
   Filter: (book_ref IS NOT NULL)

Планировщик справедливо решил, что достаточно один раз сканировать таблицу.

Этой оптимизацией управляет параметр enable_self_join_removal. Если его отключить, то увидим план запроса в предыдущих версиях:

17=# SET enable_self_join_removal = off;

17=# EXPLAIN (costs off)
WITH b AS (
    SELECT book_ref FROM bookings
)
SELECT *
FROM bookings
WHERE book_ref IN (SELECT book_ref FROM b);

                       QUERY PLAN                       
--------------------------------------------------------
 Hash Join
   Hash Cond: (bookings.book_ref = bookings_1.book_ref)
   ->  Seq Scan on bookings
   ->  Hash
         ->  Seq Scan on bookings bookings_1

Планировщик: статистика материализованных CTE
commit: f7816aec

Добавим материализацию CTE в предыдущем примере и посмотрим на план выполнения запроса в 16-й версии (с отключенным jit):

16=# EXPLAIN (analyze,timing off)
WITH b AS MATERIALIZED (
    SELECT book_ref FROM bookings
)
SELECT *
FROM bookings
WHERE book_ref IN (SELECT book_ref FROM b);

                                                    QUERY PLAN                                                     
-------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=82058.51..83469.66 rows=1055555 width=21) (actual rows=2111110 loops=1)
   CTE b
     ->  Seq Scan on bookings bookings_1  (cost=0.00..34558.10 rows=2111110 width=7) (actual rows=2111110 loops=1)
   ->  HashAggregate  (cost=47499.98..47501.98 rows=200 width=28) (actual rows=2111110 loops=1)
         Group Key: b.book_ref
         Batches: 141  Memory Usage: 11113kB  Disk Usage: 57936kB
         ->  CTE Scan on b  (cost=0.00..42222.20 rows=2111110 width=28) (actual rows=2111110 loops=1)
   ->  Index Scan using bookings_pkey on bookings  (cost=0.43..8.37 rows=1 width=21) (actual rows=1 loops=2111110)
         Index Cond: (book_ref = b.book_ref)
 Planning Time: 0.237 ms
 Execution Time: 10847.689 ms

Планировщик посчитал хорошей идеей сгруппировать CTE по book_ref, прежде чем соединять с внешним запросом (узел HashAggregate). Но book_ref — это первичный ключ таблицы bookings, поэтому количество строк после группировки не изменится, те же ~2 миллиона, а не 200 строк, как в оценке. Результат этой ошибки ― неправильно выбранной способ соединения (nested loop) CTE и внешнего запроса.

В 17-й версии планировщик может получить некоторую статистику о столбцах, входящих в материализованные CTE, и использовать её во внешних частях плана. Это позволяет точнее определять кардинальность соединений. Вот как теперь выполняется тот же запрос:

                                                    QUERY PLAN                                                     
-------------------------------------------------------------------------------------------------------------------
 Hash Semi Join  (cost=117601.18..222997.93 rows=2111110 width=21) (actual rows=2111110 loops=1)
   Hash Cond: (bookings.book_ref = b.book_ref)
   CTE b
     ->  Seq Scan on bookings bookings_1  (cost=0.00..34558.10 rows=2111110 width=7) (actual rows=2111110 loops=1)
   ->  Seq Scan on bookings  (cost=0.00..34558.10 rows=2111110 width=21) (actual rows=2111110 loops=1)
   ->  Hash  (cost=42222.20..42222.20 rows=2111110 width=28) (actual rows=2111110 loops=1)
         Buckets: 131072  Batches: 32  Memory Usage: 3529kB
         ->  CTE Scan on b  (cost=0.00..42222.20 rows=2111110 width=28) (actual rows=2111110 loops=1)
 Planning Time: 0.127 ms
 Execution Time: 1556.576 ms

Без лишней группировки, соединяя CTE и внешний запрос методом хеширования, запрос выполняется ~ в 7 раз быстрее.

Планировщик: доступ к таблице по нескольким условиям
commit: 5d8aa8bc

Нужно ли обращаться к таблице, для которой в запросе есть несколько условий? Небольшая оптимизация планировщика на эту тему.

Запрос в 16-й версии.

16=# EXPLAIN (costs off, analyze, timing off, summary off)
SELECT *
FROM tickets
WHERE ticket_no = '0005432' AND ticket_no = '0005000';

                           QUERY PLAN                            
-----------------------------------------------------------------
 Result (actual rows=0 loops=1)
   One-Time Filter: false
   ->  Index Scan using tickets_pkey on tickets (never executed)
         Index Cond: (ticket_no = '0005432'::bpchar)

Два условия для таблицы tickets противоречат друг другу, но планировщик не сразу об этом догадался и успел определить метод доступа к таблице. На более поздних этапах стало ясно, что к таблице не нужно обращаться, но усилия на планирование уже потрачены.

План этого же запроса в 17-й версии:

           QUERY PLAN           
--------------------------------
 Result (actual rows=0 loops=1)
   One-Time Filter: false

Оба условия проанализированы заранее. В результате затраты на построение плана сократились, да и выглядит аккуратнее.

Оптимизация просмотра индекса при поиске по диапазону
commit: e0b1ee17

Если для поиска по диапазону используется индекс типа B-дерево, то в каждой просмотренной странице индекса нужно найти значения, удовлетворяющие заданному диапазону.

В предыдущих версиях все значения индексных страниц проверялись на принадлежность диапазону. А начиная с 17-й версии выполняется предварительная проверка: если последнее значение на странице попадает в диапазон, то и все значения на этой странице тоже в него попадут и их можно не проверять. Чем больше страниц индекса нужно просмотреть и чем медленнее оператор сравнения, тем больший эффект от оптимизации.

В следующем примере просматривается более 40 тысяч индексных страниц. Запрос в 16-й версии:

16=# EXPLAIN (analyze, buffers, costs off, timing off)
SELECT *
FROM tickets
WHERE ticket_no > '0005432' AND ticket_no < '0005434';

                                     QUERY PLAN                                      
-------------------------------------------------------------------------------------
 Index Scan using tickets_pkey on tickets (actual rows=1469571 loops=1)
   Index Cond: ((ticket_no > '0005432'::bpchar) AND (ticket_no < '0005434'::bpchar))
   Buffers: shared hit=10675 read=30185
 Planning:
   Buffers: shared read=4
 Planning Time: 0.214 ms
 Execution Time: 683.801 ms

Этот же запрос в 17-й версии выполняется существенно быстрее:

                                     QUERY PLAN                                      
-------------------------------------------------------------------------------------
 Index Scan using tickets_pkey on tickets (actual rows=1469571 loops=1)
   Index Cond: ((ticket_no > '0005432'::bpchar) AND (ticket_no < '0005434'::bpchar))
   Buffers: shared hit=10690 read=30170
 Planning:
   Buffers: shared read=4
 Planning Time: 0.268 ms
 Execution Time: 237.177 ms

dblink, postgres_fdw: детализированные события ожидания
commit: c789f0f6, d61f2538

Расширения dblink и postgres_fdw первыми воспользовались новым интерфейсом для создания событий ожидания. Описание добавленных событий ожидания можно найти в документации к каждому расширению.

Логическая репликация: перенос слотов репликации при обновлении сервера публикации
commit: 29d0a77f

Обновление сервера публикации на новую версию утилитой pg_upgrade ― одно из узких мест логической репликации. Публикации переносятся на новую версию, а вот слоты репликации нет. Это заставляет подписчиков заново синхронизировать данные после обновления сервера публикации.

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

Журналирование использования слотов репликации
commit: 7c3fb505

Включение параметра log_replication_commands, в дополнение к регистрации команд репликации, приведет к записи в журнал информации о получении/освобождении слотов репликации процессами wal sender.

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

При включенном log_replication_commands сделаем резервную копию утилитой pg_basebackup. Теперь в журнале сервера можно найти новые сообщения:

LOG:  acquired physical replication slot "pg_basebackup_339820"
…
LOG:  released physical replication slot "pg_basebackup_339820"

Unicode: новые информационные функции
commit: a02b37fc

Функция unicode_assigned возвращает истинное значение, если всем символам в строке присвоены кодовые позиции Unicode:

SELECT unicode_assigned('Привет, Мир!');

 unicode_assigned
------------------
 t

Еще две информационные функции возвращают версию Unicode для PostgreSQL и ICU соответственно:

SELECT unicode_version(), icu_unicode_version();

 unicode_version | icu_unicode_version
-----------------+---------------------
 15.1            | 14.0

Новая функция xmltext
commit: 526fe0d7

Функция xmltext определена в стандарте SQL. Она конвертирует входную строку в значение XML, должным образом экранируя специальные символы:

SELECT xmltext('<Привет & Мир>');

         xmltext          
--------------------------
 &lt;Привет &amp; Мир&gt;

Поддержка AT LOCAL
commit: 97957fdb

Конструкция AT TIME ZONE позволяет явно указать часовой пояс для конвертации значений. В стандарте SQL имеется сокращение AT LOCAL для указания текущего часового пояса (установленного в параметре timezone). Теперь AT LOCAL можно использовать в PostgreSQL. Следующие два выражения эквивалентны для часового пояса 'Europe/Moscow':

SELECT  now() AT TIME ZONE 'Europe/Moscow',
        now() AT LOCAL
\gx

-[ RECORD 1 ]------------------------
timezone | 2023-12-18 12:57:29.612578
timezone | 2023-12-18 12:57:29.612578

Бесконечные интервалы
commit: 519fc1bd

Тип interval понимает бесконечные значения:

SELECT 'infinity'::interval, '-infinity'::interval;

 interval | interval  
----------+-----------
 infinity | -infinity

Что позволяет выполнять некоторые арифметические операции:

SELECT now() + 'infinity'::interval,
       now() - 'infinity'::interval;

 ?column? | ?column?  
----------+-----------
 infinity | -infinity

ALTER SYSTEM с неизвестными пользовательскими параметрами
commit: 2d870b4a

Команда ALTER SYSTEM научилась записывать в postgresql.auto.conf пользовательские параметры:

=# ALTER SYSTEM SET myapp.today = '2023-12-06';
ALTER SYSTEM

По умолчанию это может сделать только суперпользователь, но права на работу с отдельными параметрами можно передать командой GRANT .. ON PARAMETER.

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

Опять же, никто не запрещает записывать пользовательские параметры в основной конфигурационный файл postgresql.conf.

Сборка сервера из исходных кодов
commit: 721856ff

В tar-архивах с исходным кодом больше не будет файлов, предварительно сгенерированных утилитами Flex, Bison, perl. Также не будет сгенерированных файлов документации и страниц man. Поэтому сборка из исходных кодов, полученных из раздела Downloads, не будет отличаться от сборки из исходных кодов, полученных из git-репозитория. А это значит, что в любом случае для сборки потребуются flex, bison и perl.

Сделано в первую очередь для сборочной системы meson, в которой затруднительно собирать сервер из tar-архивов в нынешнем виде.

В этом году всё. Ждем следующего, январского коммитфеста 17-й версии.

PostgreSQL 17: Часть 3 или Коммитфест 2023-11