Продолжаем следить за новинками 16-й версии. В начале февраля завершился четвертый коммитфест. Что нового нас ждет впереди?

Напомню, что самое интересное из первых трех коммитфестов можно прочитать в предыдущих статьях серии: 2022-07, 2022-09, 2022-11.

В этот обзор попали следующие разработки:

Новая функция random_normal
Входные форматы для целочисленных констант
Прощай postmaster
Параллельное выполнение агрегатных функций string_agg и array_agg
Новый параметр enable_presorted_aggregate
Вспомогательные функции планировщика для оконных функций
Оптимизация группировки повторяющихся столбцов в GROUP BY и DISTINCT
Параметры VACUUM: SKIP_DATABASE_STATS и ONLY_DATABASE_STATS
pg_dump: блокировка таблиц пакетами
PL/pgSQL: инициализация курсорных переменных
Роли с атрибутом CREATEROLE
Установка параметров на уровне базы данных и пользователя
Новый параметр: reserved_connections
postgres_fdw: использование TABLESAMPLE для сбора статистики по сторонним таблицам
postgres_fdw: пакетный режим вставки записей при обновлении ключа секционирования
pg_ident.conf: способы указания пользователя PostgreSQL
Нормализация DDL и служебных команд
Новая функция bt_multi_page_stats
Новая функция pg_split_walfile_name
pg_walinspect, pg_waldump: получение образов страниц из WAL

Новая функция random_normal
commit: 38d81760

В дополнение к функции random, возвращающей равномерно распределенное случайное число в диапазоне 0… 1, появилась новая функция для получения нормально распределенных случайных чисел: random_normal. У функции два параметра: математическое ожидание и стандартное отклонение.

Сгенерируем 1 000 000 чисел и проверим правило, известное как 68-95-99.7. Согласно этому правилу 68% нормально распределенных случайных чисел не отличаются от математического ожидания (первый параметр) более чем на одно стандартное отклонение (второй параметр) как в плюс, так и в минус, 95% ― более чем на 2 стандартных отклонения и 99.7% ― более чем на 3.

WITH samples AS (
    SELECT random_normal(0.0, 1.0) AS x FROM generate_series(1,1000000)
)
SELECT count(x) FILTER (WHERE x BETWEEN -1 AND 1)/1000000.0 AS stddev_1,
       count(x) FILTER (WHERE x BETWEEN -2 AND 2)/1000000.0 AS stddev_2,
       count(x) FILTER (WHERE x BETWEEN -3 AND 3)/1000000.0 AS stddev_3
FROM samples;

        stddev_1        |        stddev_2        |        stddev_3        
------------------------+------------------------+------------------------
 0.68391100000000000000 | 0.95473800000000000000 | 0.99724900000000000000

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

WITH samples AS (
    SELECT random_normal(0.0, 1.0) AS x FROM generate_series(1,1000000)
)
SELECT round(x::numeric,1) point, count(*) AS density
FROM samples
GROUP BY point
ORDER BY point
\g (format=unaligned tuples_only=on fieldsep='\t') | gnuplot -e "set term png; set output 'data.png'; plot '<cat'"

Здесь для получения плотности распределения данные группируются с шагом 0.1. В результате в файле data.png получаем знаменитый график в форме колокола:

Надо отметить, что в расширении tablefunc уже есть функция normal_rand для получения нормально распределенных случайных чисел. Теперь подобная функция доступна без установки расширений.

См. также
Waiting for PostgreSQL 16 – Invent random_normal() to provide normally-distributed random numbers (Hubert 'depesz' Lubaczewski)

Входные форматы для целочисленных констант
commit: 6fcda9ab, 6dfacbf7, faff8f8e

В соответствии с последними изменениями в стандарте SQL, целочисленные константы можно записывать не только в десятичном, но и в шестнадцатеричном, восьмеричном и двоичном виде:

SELECT 0x2A hex_int, 0o52 oct_int, 0b101010 bin_int;

 hex_int | oct_int | bin_int
---------+---------+---------
      42 |      42 |      42

Если получившееся целое число не помещается в bigint, то оно преобразуется в numeric (второй коммит). Кроме того, для больших целочисленных значений можно использовать символ подчеркивания для визуального разделения групп цифр (третий коммит):

SELECT count(*) FROM generate_series(1, 1_000_000);

  count  
---------
 1000000

См. также
Waiting for PostgreSQL 16 – Non-decimal integer literals (Hubert 'depesz' Lubaczewski)
Waiting for PostgreSQL 16 – Allow underscores in integer and numeric constants (Hubert 'depesz' Lubaczewski)
hex, oct, bin integers in PostgreSQL 16 (Pavlo Golub)

Прощай postmaster
commit: 81266442, 37e26733

«При старте сервера запускается процесс, традиционно называемый postmaster...»

С похожей фразы начинаются многие учебные материалы, посвященные архитектуре PostgreSQL. Но имя процесса postmaster уже 15 лет как считается устаревшим, а реальное имя ― postgres. Упоминания о postmaster можно найти в документации, а в каталоге bin есть файл postmaster с символьной ссылкой на postgres.

В 16-й версии пришла пора окончательно попрощаться с postmaster. Все упоминания о нем удалены.

Параллельное выполнение агрегатных функций string_agg и array_agg
commit: 16fd03e9

До 16-й версии запросы с функциями string_agg и array_agg всегда выполнялись последовательно:

15=# EXPLAIN (costs off)
SELECT fare_conditions, array_agg(flight_id), string_agg(ticket_no, ',')
FROM ticket_flights
GROUP BY fare_conditions;

            QUERY PLAN            
----------------------------------
 HashAggregate
   Group Key: fare_conditions
   ->  Seq Scan on ticket_flights

Теперь стал возможен и параллельный план:

16=# EXPLAIN (costs off)
SELECT fare_conditions, array_agg(flight_id), string_agg(ticket_no, ',')
FROM ticket_flights
GROUP BY fare_conditions;

                         QUERY PLAN                          
-------------------------------------------------------------
 Finalize GroupAggregate
   Group Key: fare_conditions
   ->  Gather Merge
         Workers Planned: 2
         ->  Sort
               Sort Key: fare_conditions
               ->  Partial HashAggregate
                     Group Key: fare_conditions
                     ->  Parallel Seq Scan on ticket_flights

Новый параметр enable_presorted_aggregate
commit: 4a29eabd, 3226f472

Первый коммит уменьшает оценку стоимости инкрементальной сортировки. Теперь планировщик будет чаще выбирать этот метод сортировки. В том числе и для оптимизации агрегатных функций с ORDER BY и DISTINCT.

Но в некоторых случаях неравномерного распределения данных в группах агрегаты с ORDER BY или DISTINCT будут проигрывать от использования инкрементальной сортировки. Отключение нового параметра enable_presorted_aggregate (второй коммит) поможет вернуть план из предыдущих версий.

Вспомогательные функции планировщика для оконных функций
commit: ed1a88dd

Если для оконной функции рамка окна не задана явно, то по умолчанию используется RANGE UNBOUNDED PRECEDING.

15=# EXPLAIN (costs off, analyze, timing off)
SELECT row_number() OVER (ORDER BY ticket_no)
FROM tickets;

                                    QUERY PLAN                                     
-----------------------------------------------------------------------------------
 WindowAgg (actual rows=2949857 loops=1)
   ->  Index Only Scan using tickets_pkey on tickets (actual rows=2949857 loops=1)
         Heap Fetches: 0
 Planning Time: 0.098 ms
 Execution Time: 682.466 ms

Для этого запроса можно задать и другой вариант рамки ROWS UNBOUNDED PRECEDING. На результат это не окажет влияния, но производительность увеличится:

15=# EXPLAIN (costs off, analyze, timing off)
SELECT row_number() OVER (ORDER BY ticket_no ROWS UNBOUNDED PRECEDING)
FROM tickets;

                                    QUERY PLAN                                     
-----------------------------------------------------------------------------------
 WindowAgg (actual rows=2949857 loops=1)
   ->  Index Only Scan using tickets_pkey on tickets (actual rows=2949857 loops=1)
         Heap Fetches: 0
 Planning Time: 0.100 ms
 Execution Time: 483.560 ms

Разница во времени объясняется тем, что в случае рамки с RANGE необходимо дополнительно проверять родственные строки, что не нужно в варианте с ROWS. Получается, что поведение по умолчанию может быть более затратным.

В 16-й версии добавили вспомогательные функции планировщика для оконных функций row_number, rank, dense_rank, percent_rank, cume_dist и ntile. С помощью подсказок этих функций, при выполнении запроса будет выбираться оптимальный вариант указания рамки окна.

Оптимизация группировки повторяющихся столбцов в GROUP BY и DISTINCT
commit: 8d83a5d0

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

Оптимизацию можно увидеть на таком примере.

SET max_parallel_workers_per_gather = 0;
SET jit = off;

15=# EXPLAIN (costs off, analyze)
SELECT b.seat_no, s.seat_no
FROM boarding_passes b
     JOIN seats s ON b.seat_no = s.seat_no
GROUP BY b.seat_no, s.seat_no;

                                               QUERY PLAN                                                
---------------------------------------------------------------------------------------------------------
 Group (actual time=3537.559..10758.307 rows=461 loops=1)
   Group Key: b.seat_no, s.seat_no
...

Остальная часть плана не представляет интереса и опущена.

Первые две строки плана этого же запроса в 16-й версии:

                                                QUERY PLAN                                                 
-----------------------------------------------------------------------------------------------------------
 HashAggregate (actual time=8201.412..8201.440 rows=461 loops=1)
   Group Key: b.seat_no
...

Главное различие во второй строке Group Key. В 16-й версии планировщик знает, что достаточно группировать по одному столбцу, и исходя из этого выбирает один план, а в 15-й версии группировка выполняется по обоим столбцам с более худшим общим планом (видно по времени выполнения).

Аналогичная оптимизация сделана для DISTINCT с повторяющимися столбцами.

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

Параметры VACUUM: SKIP_DATABASE_STATS и ONLY_DATABASE_STATS
commit: a46a7011

В конце своей работы процесс очистки обновляет статистику базы данных: счетчик замороженных транзакций (pg_database.datfrozenxid) и счетчик мультитранзакций (pg_database.datminmxid). Для этого требуется полностью просканировать pg_class в поисках минимальных значений relfrozenxid и relminmxid. Если найденное минимальное значение любого из счетчиков больше текущего значения для базы данных, то выполняется обновление. Чем больше в базе данных таблиц ― тем дольше выполняется это финальное действие.

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

Утилита vaccumdb (в версиях с 12-й по 15-ю) выполняет VACUUM для каждой таблицы базы данных. Если таблиц много (тысячи, десятки тысяч), то вышеописанные причины делают ее работу крайне неэффективной. Не спасает и распараллеливание работы (параметр -j), ведь обновление счетчиков базы данных может выполняться одновременно только в одном процессе.

В 16-й версии команде VACUUM добавили два логических параметра:

SKIP_DATABASE_STATS ― выполняются все действия по очистке, кроме обновления счетчиков базы данных;
ONLY_DATABASE_STATS ― действия по очистке не выполняются, только обновляются счетчики базы данных.

Теперь vaccumdb выполняет очистку таблиц без обновления статистики базы данных, а по окончании очистки таблиц отдельно выполняет:

VACUUM(ONLY_DATABASE_STATS)

pg_dump: блокировка таблиц пакетами
commit: 5f53b42c

В начале работы pg_dump составляет список таблиц для выгрузки и последовательно блокирует отдельными командами LOCK TABLE каждую из них в режиме ACCESS SHARE. Если таблиц очень много (десятки, сотни тысяч) и pg_dump запускается удаленно, то суммарные сетевые задержки на выполнение блокировок могут быть значительными.

Теперь pg_dump блокирует таблицы одной командой LOCK TABLE, в которой через запятую перечислены все таблицы. (Команда может быть разбита на несколько, так чтобы длина каждой команды не превышала 100 000 символов.)

PL/pgSQL: инициализация курсорных переменных
commit: d747dc85

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

15=# DO $$ DECLARE                               
    c CURSOR FOR SELECT 1; -- связанный с запросом курсор
    rc refcursor; -- несвязанный курсор
BEGIN
    RAISE NOTICE 'c: %, rc: %', c, rc;
    OPEN c;
    OPEN rc FOR SELECT 2;
    RAISE NOTICE 'c: %, rc: %', c, rc;
END;$$;

NOTICE:  c: c, rc: <NULL>
NOTICE:  c: c, rc: <unnamed portal 7>
DO

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

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

CREATE PROCEDURE proc_1() AS
$$DECLARE
    c CURSOR FOR SELECT 1;
BEGIN
    FOR r IN c LOOP
        RAISE NOTICE 'r: %, c: %', r, c;
    END LOOP;
END;$$ LANGUAGE plpgsql;

CREATE PROCEDURE proc_2() AS
$$DECLARE
    c CURSOR FOR SELECT 2;
BEGIN
    FOR r IN c LOOP
        RAISE NOTICE 'r: %, c: %', r, c;
        CALL proc_1();
    END LOOP;
END;$$ LANGUAGE plpgsql;

Несмотря на то, что курсорная переменная в обоих случаях объявлена локально, вызов второй процедуры приводит к ошибке:

15=# CALL proc_2();

NOTICE:  r: (2), c: c
ERROR:  cursor "c" already in use
CONTEXT:  PL/pgSQL function proc_1() line 4 at FOR over cursor
SQL statement "CALL proc_1()"
PL/pgSQL function proc_2() line 6 at CALL

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

16=# CALL proc_2();

NOTICE:  r: (2), c: <unnamed portal 1>
NOTICE:  r: (1), c: <unnamed portal 2>
CALL

Роли с атрибутом CREATEROLE
commit: cf5eb37c, f1358ca5, e5b8a4c0

Для управления ролями без прав суперпользователя можно создать обычную роль c атрибутом CREATEROLE. При этом выдавать атрибут SUPERUSER такая роль не сможет, если сама им не обладает.

Но проблема в том, что эту защиту очень легко преодолеть.

15-postgres=# CREATE ROLE admin LOGIN CREATEROLE;

CREATE ROLE

15-postgres=# \c - admin

You are now connected to database "postgres" as user "admin".

15-admin=> CREATE ROLE bob LOGIN;

CREATE ROLE

15-admin=> GRANT pg_execute_server_program TO bob;

GRANT ROLE

Всё. Теперь bob может выполнять команды операционной системы с правами владельца сервера.

Проверяем, что изменилось в 16-й версии.

16-postgres=# CREATE ROLE admin LOGIN CREATEROLE;

CREATE ROLE

16-postgres=# \c - admin

You are now connected to database "postgres" as user "admin".

16-admin=> CREATE ROLE bob LOGIN;

CREATE ROLE

16-admin=> \du

                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 admin     | Create role                                                | {bob}
 bob       |                                                            | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Обратите внимание, что роль admin автоматически включена в роль bob сразу после создания роли. Причем включена с правами администрирования (WITH ADMIN OPTION). Это сделано для того, чтобы управлять ролью bob могла только роль её создавшая. Предположим у нас есть второй администратор с выделенной ролью и с атрибутом CREATEROLE.

16-postgres=# CREATE ROLE admin2 LOGIN CREATEROLE;

CREATE ROLE

16-postgres=# \c - admin2

You are now connected to database "postgres" as user "admin2".

16-admin2=> ALTER ROLE bob CONNECTION LIMIT 1;

ERROR:  permission denied

Атрибута CREATEROLE больше не достаточно для управления другими ролями.

Забудем про второго администратора и вернемся к включению роли bob в pg_execute_server_program.

16-admin=> GRANT pg_execute_server_program TO bob;

ERROR:  must have admin option on role "pg_execute_server_program"

Для выполнения команды GRANT роль admin сама должна быть включена в pg_execute_server_program и обязательно с опцией администрирования.

16-postgres=# GRANT pg_execute_server_program TO admin WITH ADMIN OPTION;

GRANT ROLE

16-admin=> GRANT pg_execute_server_program TO bob;

GRANT ROLE

Здесь используется принцип, что предоставить другим можно только то, чем обладаешь сам, и не более. Эти изменения реализованы в первом коммите.

Теперь проверим, может ли admin переключиться на роль bob?

16-admin=> SET ROLE bob;

ERROR:  permission denied to set role "bob"

Нет, не может. Для выполнения SET ROLE нужно, чтобы при включении в роль была указана опция SET. Убедимся, что её нет:

16-admin=> SELECT roleid::regrole, member::regrole, grantor::regrole,
       admin_option, inherit_option, set_option
FROM pg_auth_members
WHERE roleid = 'bob'::regrole\gx

-[ RECORD 1 ]--+---------
roleid         | bob
member         | admin
grantor        | postgres
admin_option   | t
inherit_option | f
set_option     | f

Как видно, включена лишь опция ADMIN, позволяющая управлять ролью. Выключенная опция INHERIT говорит о том, что роль admin не сможет наследовать привилегии роли bob. А выключенная опция SET не позволит роли admin переключиться на роль bob.

Что делать в таком случае? Роль admin может еще раз включить себя в роль bob с нужными опциями:

16-admin=> GRANT bob TO admin WITH INHERIT TRUE, SET TRUE;

GRANT ROLE

16-admin=> SET ROLE bob;

SET

Если предполагается, что роль admin будет регулярно создавать новые роли и ей нужны будут опции SET и/или INHERIT в них, то процесс можно автоматизировать при помощи нового параметра createrole_self_grant (второй коммит).

16-admin=> SET createrole_self_grant = 'INHERIT, SET';

SET

16-admin=> CREATE ROLE alice LOGIN;

CREATE ROLE

16-admin=> SELECT roleid::regrole, member::regrole, grantor::regrole,
       admin_option, inherit_option, set_option
FROM pg_auth_members
WHERE roleid = 'alice'::regrole AND grantor = 'admin'::regrole\gx

-[ RECORD 1 ]--+------
roleid         | alice
member         | admin
grantor        | admin
admin_option   | f
inherit_option | t
set_option     | t

Нужные опции членства в роли alice установлены.

Третий коммит разрешает устанавливать атрибуты REPLICATION, BYPASSRLS и CREATEDB другим ролям при условии, что роль admin сама ими обладает.

16-postgres=# ALTER ROLE admin CREATEDB;

ALTER ROLE

16-postgres=# \c - admin

You are now connected to database "postgres" as user "admin".

16-admin=> ALTER ROLE bob CREATEDB;

ALTER ROLE

16-admin=> ALTER ROLE bob REPLICATION;

ERROR:  must have replication privilege to change replication attribute

См. также
Surviving Without A Superuser — Coming to v16 (Robert Haas)

Установка параметров на уровне базы данных и пользователя
commit: 096dd80f

В демо-базе можно переключать язык при помощи пользовательского параметра bookings.lang. Предположим, что для пользователя bob в системе создали одноименную роль без прав суперпользователя. После подключения bob решил, что для него язык всегда должен быть en:

postgres=# \c demo bob

You are now connected to database "demo" as user "bob".

bob=> ALTER ROLE bob IN DATABASE demo SET bookings.lang = 'en';

ERROR:  permission denied to set parameter "bookings.lang"

Однако установка параметров командами ALTER ROLE|DATABASE… SET требует прав суперпользователя. Кстати, с 15-й версии права на отдельные параметры (в том числе пользовательские) можно выдать командой GRANT… ON PARAMETER. Но в данном случае такое поведение кажется не совсем логичным.

В 16-й версии появилась возможность явно указать, что параметр должен устанавливаться от имени обычной роли без прав суперпользователя:

bob=> ALTER ROLE bob IN DATABASE demo SET bookings.lang = 'en' USER SET;

ALTER ROLE

bob=> \c demo bob

You are now connected to database "demo" as user "bob".

bob=> SHOW bookings.lang;

 bookings.lang
---------------
 en

В таблице pg_db_role_setting добавлен соответствующий флаг, который можно увидеть командой \drds:

bob=> \drds

                     List of settings
 Role | Database |           Settings           | User set
------+----------+------------------------------+----------
 bob  | demo     | bookings.lang=en             | t
      | demo     | search_path=bookings, public+| f       +
      |          | bookings.lang=ru             | f

Новый параметр: reserved_connections
commit: 6e2775e4

Благодаря ненулевому значению параметра superuser_reserved_connections (по умолчанию 3) пользователи не могут занять все доступные подключения (max_connections) и у суперпользователя всегда остается возможность попасть на сервер.

Однако многие задачи регулярного обслуживания, мониторинга и резервного копирования могут выполняться обычными ролями, без прав суперпользователя. Как для них обеспечить свободные слоты для подключения?

Для этого предназначен новый параметр reserved_connections (по умолчанию 0) и новая предопределенная роль pg_use_reserved_connections. Достаточно зарезервировать нужное количество подключений в reserved_connections (требует перезапуска сервера) и роли, включенные в pg_use_reserved_connections, смогут пользоваться этим резервом.

См. также
Reserve connections for the pg_use_reserved_connections group in PostgreSQL 16 (Pavlo Golub)

postgres_fdw: использование TABLESAMPLE для сбора статистики по сторонним таблицам
commit: 8ad51b5f

Сбор статистики по сторонним таблицам большого размера выполняется не самым эффективным способом. Команда ANALYZE получает все строки удаленной таблицы и сразу отбрасывает большую часть, т.к. для статистики достаточно небольшой выборки (300 default_statistics_target*).

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

Для проверки создадим в базе postgres стороннюю таблицу tickets (~3 миллиона строк) для одноименной таблицы из базы данных demo этого же кластера баз данных.

CREATE EXTENSION postgres_fdw;

CREATE SERVER srv
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (dbname 'demo');

CREATE USER MAPPING FOR postgres
    SERVER srv
    OPTIONS (user 'postgres');

IMPORT FOREIGN SCHEMA bookings
    LIMIT TO (tickets)
    FROM SERVER srv
    INTO public;

По умолчанию семплирование для сбора статистики включено:

\timing on

ANALYZE tickets;

ANALYZE
Time: 322,737 ms

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

ALTER FOREIGN TABLE tickets OPTIONS (analyze_sampling 'off');

ANALYZE tickets;

ANALYZE
Time: 2068,078 ms (00:02,068)

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

postgres_fdw: пакетный режим вставки записей при обновлении ключа секционирования
commit: 594f8d37

Расширение postgres_fdw поддерживает пакетную вставку записей с 14-й версии. В прошлой статье говорилось о снятии одного из ограничений на использование пакетного режима для команды COPY. Но есть и другие ограничения.

Пакетный режим также был запрещен, если записи вставляются не командами INSERT и COPY. Как же еще можно вставить записи? Это может быть UPDATE ключа секционирования секционированной таблицы. В таком случае вместо изменения нужно удалить записи из одной секции и вставить их в другую. Если секция-приемник является сторонней таблицей, то пакетный режим для неё не используется.

В 16-й версии это ограничение сняли.

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

pg_ident.conf: способы указания пользователя PostgreSQL
commit: efb6f4a4

В файле pg_ident.conf имя пользователя PostgreSQL (поле PG-USERNAME) теперь обрабатывается по тем же правилам, что и в pg_hba.conf. В качестве значения можно указывать:

специальное значение all соответствует любому пользователю;
значения, начинающиеся с символа «/», считаются регулярными выражениями;
для значений, начинающихся с символа «+», выполняется проверка на членство в ролях.

Нормализация DDL и служебных команд
commit: 3db72ebc

Нормализация запроса (query jumbling) выполняется при вычислении хеш-кода, который расширение pg_stat_statements использует для группировки одинаковых запросов. Важно что для команд DML хеш-код вычисляется на основе разобранного запроса, а не текста команды. Это позволяет игнорировать регистр символов, разделители слов, заменять константы на параметры.

Но кроме команд DML, есть еще служебные команды и команды DDL, для которых хеш-код вычислялся на основе текста запроса. В 15-й версии следующие две команды в pg_stat_statements будут считаться разными, хотя отличаются только регистром символов и количеством пробелов.

DROP TABLE IF EXISTS t;
drop   table   if   exists   t;

Благодаря этому патчу, в 16-й версии ситуация изменится и мы увидим одну строчку в pg_stat_statements:

SELECT queryid, query, calls
FROM pg_stat_statements
WHERE query ILIKE 'drop table%';

       queryid       |         query          | calls
---------------------+------------------------+-------
 8953406224830875875 | DROP TABLE IF EXISTS t |     2

Надо отметить, что пространство для улучшения еще осталось. Процесс нормализации пока не умеет объединять вызов процедуры командой CALL с разными значениями параметров. Тоже самое относится к установке разных значений одного параметра командой SET.

Новая функция bt_multi_page_stats
commit: 1fd3dd20

В расширении pageinspect появилась функция bt_multi_page_stats, упрощающая получение статистики сразу по нескольким страницам Btree-индекса. Статистику одной страницы можно было и раньше получить функцией bt_page_stats. У новой функции появился третий аргумент — количество страниц. Если он отрицательный, то возвращается информация о всех страницах до конца индекса.

SELECT blkno, type, live_items
FROM bt_multi_page_stats('pg_class_oid_index', 2, -1);

 blkno | type | live_items
-------+------+------------
     2 | l    |        145
     3 | r    |          2
(2 rows)

Новая функция pg_split_walfile_name
commit: cca18634, 13e0d7a6

Некоторые сообщения об ошибках чтения/записи WAL-файлов содержат имя файла и смещение. Для того чтобы превратить эту информацию в позицию LSN, можно использовать новую функцию pg_split_walfile_name.

Для примера возьмем текущий файл WAL и смещение в 42 байта.

SELECT pg_walfile_name(pg_current_wal_lsn()) AS walfile,
       42 AS offset
\gset

Функция pg_split_walfile_name возвращает порядковый номер сегмента WAL и линию времени.

SELECT :'walfile' AS walfile, * FROM pg_split_walfile_name(:'walfile');

         walfile          | segment_number | timeline_id
--------------------------+----------------+-------------
 000000010000000100000040 |            320 |           1

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

SELECT :'walfile' AS walfile,                                              
       :offset AS offset,
       '0/0'::pg_lsn + w.segment_number*s.setting::int + :offset AS lsn
FROM pg_split_walfile_name(:'walfile') w, pg_settings s
WHERE s.name = 'wal_segment_size';

         walfile          | offset |    lsn     
--------------------------+--------+------------
 000000010000000100000040 |     42 | 1/4000002A

pg_walinspect, pg_waldump: получение образов страниц из WAL
commit: c31cf1c0, d497093c

Оба инструмента для исследования содержимого журнала предзаписи теперь могут получать образы страниц, записываемые в WAL.

Образы страниц записываются в WAL при первом изменении любой страницы после контрольной точки.Запомним позиции в WAL до и после команды UPDATE.

CREATE TABLE t AS SELECT x FROM generate_series(1,100) x;
CHECKPOINT;

SELECT pg_current_wal_lsn() AS start_lsn;

 start_lsn  
------------
 1/6DF4D250

UPDATE t SET x = x + 1;

SELECT pg_current_wal_lsn() AS end_lsn;

  end_lsn   
------------
 1/6DF51A18

Воспользуемся новой функцией pg_get_wal_fpi_info расширения pg_walinspect (первый коммит), которая покажет все образы страниц, записанные в WAL между заданными позициями LSN.

CREATE EXTENSION pg_walinspect;

SELECT lsn, reltablespace, reldatabase, relfilenode, relblocknumber, forkname,
       substr(fpi, 1, 8) AS fpi_trimmed
FROM pg_get_wal_fpi_info('1/6DF4D250', '1/6DF51A18');

    lsn     | reltablespace | reldatabase | relfilenode | relblocknumber | forkname |    fpi_trimmed     
------------+---------------+-------------+-------------+----------------+----------+--------------------
 1/6DF4D250 |          1663 |       16384 |        1259 |              1 | main     | \x01000000002ef46d
 1/6DF4E9A8 |          1663 |       16384 |        1249 |             60 | main     | \x01000000c0b6f46d
 1/6DF4EF58 |          1663 |       16384 |       16678 |              0 | main     | \x0100000008d0f46d
(3 rows)

А теперь получим эти же страницы утилитой pg_waldump. Для нового параметра save-fullpage (второй коммит) укажем имя каталога, где будут созданы отдельные файлы для каждой страницы.

$ pg_waldump --start=1/6DF4D250 --end=1/6DF51A18 --quiet --save-fullpage=./waldump
$ ls -1 -s -h ./waldump

total 24K
8,0K 00000001-6DF4D250.1663.16384.1259.1_main
8,0K 00000001-6DF4E9A8.1663.16384.1249.60_main
8,0K 00000001-6DF4EF58.1663.16384.16678.0_main

На этом пока всё. Ждем результатов следующего и последнего для 16-й версии мартовского коммитфеста.

PostgreSQL 16: Часть 4 или Коммитфест 2023-01