PostgreSQL 15 уже вышел официально. И в сети появилось множество информации о новинках версии.

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

Самое интересное из первого, июльского, коммитфеста можно прочитать в предыдущей статье серии: 2022-07.

PostgreSQL 15

Но начнем с 15-й версии. У нас потери: в последний момент откатили еще две разработки:

Поддержка стандарта SQL/JSON
Оптимизация GROUP BY

PostgreSQL 16

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

Функция SYSTEM_USER
Информация о замороженных страницах и строках в отчете об очистке
Функция pg_stat_get_backend_idset возвращает реальный внутренний идентификатор сеанса
Оптимизация ORDER BY и DISTINCT для агрегатных функций
Ускорение массовой вставки в секционированную таблицу
Оптимизация поиска в снимке данных
Двунаправленная логическая репликация
pg_auth_members: управление предоставлением членства в ролях
pg_auth_members: членство в ролях и наследование привилегий
pg_receivewal, pg_recvlogical: обработка сигнала SIGTERM

Функция SYSTEM_USER
commit: 0823d061

Новая функция является частью стандарта SQL, но не была реализована в PostgreSQL. Функция показывает информацию о методе аутентификации и внешнем пользователе:

SELECT session_user, system_user;

 session_user | system_user  
--------------+--------------
 alice        | peer:student

Из примера видно, что при создании сеанса alice использовался метод аутентификации peer, а в качестве внешнего имени student (пользователь ОС). Для метода безусловной аутентификации trust функция вернет NULL.

Подробную информацию о подключении можно также получить из журнала сервера, после включения параметра log_connections.

Информация о замороженных страницах и строках в отчете об очистке
commit: d977ffd9

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

CREATE TEMP TABLE t AS SELECT 42;

VACUUM (freeze, verbose) t;

INFO:  aggressively vacuuming "demo.pg_temp_3.t"
INFO:  finished vacuuming "demo.pg_temp_3.t": index scans: 0
pages: 0 removed, 1 remain, 1 scanned (100.00% of total)
tuples: 0 removed, 1 remain, 0 are dead but not yet removable
removable cutoff: 945, which was 0 XIDs old when operation ended
new relfrozenxid: 945, which is 1 XIDs ahead of previous value
frozen: 1 pages from table (100.00% of total) had 1 tuples frozen
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
avg read rate: 91.912 MB/s, avg write rate: 0.000 MB/s
buffer usage: 5 hits, 4 misses, 0 dirtied
WAL usage: 1 records, 0 full page images, 188 bytes
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
VACUUM

Новая строка начинается с «frozen:».

Функция pg_stat_get_backend_idset возвращает реальный внутренний идентификатор сеанса
commit: d7e39d72

Предположим что в системе есть несколько сеансов, работающих с временными таблицами, размещенными во временных схемах. Список временных схем:

SELECT n.nspname,
       regexp_replace(n.nspname, '^pg_temp_', '')::int AS backend_id
FROM   pg_namespace n                                                    
WHERE  n.nspname ~ '^pg_temp_';

  nspname  | backend_id
-----------+------------
 pg_temp_5 |        5
 pg_temp_3 |        3
 pg_temp_4 |        4
(3 rows)

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

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

Для ответа на этот вопрос была доработана функция pg_stat_get_backend_idset и связанные с ней статистические функции. Раньше функция возвращала в качестве идентификаторов сеансов индексы массива открытых сеансов. Однако после открытия/закрытия сеансов и перечитывания статистики значение индекса отдельного сеанса могло изменяться. Теперь pg_stat_get_backend_idset стабильно возвращает реальные внутренние идентификаторы сеансов, которые можно использовать для соединения с backend_id из предыдущего запроса.

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

WITH tmp AS (
    SELECT n.nspname,                                            
           regexp_replace(n.nspname, '^pg_temp_', '')::int AS backend_id
    FROM   pg_namespace n
    WHERE  n.nspname ~ '^pg_temp_'
)
SELECT pg_stat_get_backend_pid(backend_id) AS pid,
       nspname
FROM   tmp
       LEFT JOIN pg_stat_get_backend_idset() AS b(backend_id)
           USING (backend_id);

  pid   |  nspname  
--------+-----------
 336661 | pg_temp_3
 349872 | pg_temp_4
        | pg_temp_5
(3 rows)

Заодно отмечаем, что схема pg_temp_5 в настоящий момент не используется.

Оптимизация ORDER BY и DISTINCT для агрегатных функций
commit: 1349d279

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

Рассмотрим такой запрос:

SELECT flight_no,
       array_agg(status ORDER BY status),
       array_agg(DISTINCT status)
FROM flights
GROUP BY flight_no;

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

CREATE INDEX ON flights (flight_no, status);

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

Как результат, этот запрос выполняется на моем ноутбуке в 16-й версии примерно в полтора раза быстрее, чем в аналогичных условиях и с таким же планом в 15-й версии.

Ускорение массовой вставки в секционированную таблицу
commit: 3592e0ff

Патч ускоряет массовую вставку или обновление записей при использовании секционирования. Команды, которые массово вставляют записи в одну и ту же секцию будут тратить меньше времени на поиск секции для каждой записи. Если 16 (константа в коде) записей подряд попадают в одну и ту же секцию, то имя секции кешируется, чтобы для последующих записей сначала проверить именно эту секцию.

Оптимизация работает для таблиц, секционированных по диапазону или списку значений. Для секционирования по хешу она не имеет смысла.

Тесты разработчиков показали ~15-20% сокращение времени выполнения.

Оптимизация поиска в снимке данных
commit: 37a6e5df, b6ef1675, 82739d4a

Оптимизирован линейный поиск по массиву (первый коммит) для платформ x86-64 (второй коммит) и arm (третий коммит). Новый алгоритм, использующий инструкции SIMD, применили к поиску в списке активных транзакций снимка (snapshot->xip array). При большом (сотни) количестве одновременных пишущих транзакций это дает ощутимый выигрыш командам в определении правил видимости строк, а следовательно и выигрыш в общей производительности.

Двунаправленная логическая репликация
commit: 36628396

Двунаправленной репликацией называется ситуация, когда первый сервер подписывается на изменения таблицы вторым сервером, а второй сервер подписывается на изменения этой же таблицы первым сервером. В итоге изменения, выполненные на любом из серверов, будут видны на обоих.

Реализовать такую схему работы в предыдущих версиях PostgreSQL нельзя. Причина в том, что сервер-подписчик принимает любые изменения публикующего сервера: как команды SQL, так и реплицированные изменения. Когда первый сервер выполняет команду SQL, изменения передаются и применяются на втором сервере. И эти же самые изменения опять передаются первому серверу, образуя замкнутый круг репликации. Поэтому при создании второй подписки такая ситуация отслеживается и команда CREATE SUBSCRIPTION завершается ошибкой.

Для реализации двунаправленной репликации нужен способ, при котором передаются только изменения, сделанные командами SQL, но не пришедшие по репликации. И такой способ появился.

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

Настроим двунаправленную логическую репликацию таблицы на двух узлах (node_1 и node_2), расположенных на одном сервере и на разных портах 5401 и 5402 соответственно.

Параметр wal_level должен поддерживать логическую репликацию на всех узлах:

node_1=# SHOW wal_level;

wal_level 
-----------
 logical
(1 row)

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

node_1=# CREATE TABLE test (
             node text DEFAULT 'node_1',
             id int,
             PRIMARY KEY (node, id)
         );
node_1=# CREATE PUBLICATION pub1 FOR TABLE test;

На втором узле создаем таблицу с такой же структурой и публикацию:

node_2=# CREATE TABLE test (
             node text DEFAULT 'node_2',
             id int,
             PRIMARY KEY (node, id)
         );
node_2=# CREATE PUBLICATION pub2 FOR TABLE test;

Первый сервер подписывается на публикацию второго сервера, а второй сервер — на публикацию первого.

node_1=# CREATE SUBSCRIPTION sub1_pub2
             CONNECTION 'port=5402 user=postgres dbname=postgres'
             PUBLICATION pub2
             WITH (copy_data = false, origin = none);
node_2=# CREATE SUBSCRIPTION sub2_pub1
             CONNECTION 'port=5401 user=postgres dbname=postgres'
             PUBLICATION pub1
             WITH (copy_data = false, origin = none);

Важные моменты при настройке:

• origin = none ― новый параметр подписки, именно он обеспечивает обработку циклов репликации. Указывает публикующему серверу отправлять только изменения, не отмеченные источником репликации, то есть сделанные командами SQL. Второе значение параметра ― any ― используется по умолчанию и соответствует поведению в предыдущих версиях.
• copy_data = false ― начальную синхронизацию данных в таблицах нужно выполнить вручную. Либо, как в нашем случае, таблицы должны быть пустыми.
• Таблицы не должны изменяться, пока настройка не закончена.

Добавим по строке в каждую таблицу:

node_1=# INSERT INTO test (id) VALUES (1);
node_2=# INSERT INTO test (id) VALUES (1);

Проверяем содержимое.

node_1=# SELECT * FROM test ORDER BY 1,2;

 node   | id 
--------+----
 node_1 |  1
 node_2 |  1
(2 rows)

node_2=# SELECT * FROM test ORDER BY 1,2;

 node   | id 
--------+----
 node_1 |  1
 node_2 |  1
(2 rows)

Двунаправленная репликация работает!

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

node_3=# CREATE TABLE test (
             node text DEFAULT 'node_3',
             id int,
             PRIMARY KEY (node, id)
         );
node_3=# CREATE PUBLICATION pub3 FOR TABLE test;

Первые два узла подписываются на публикацию третьего.

node_1=# CREATE SUBSCRIPTION sub1_pub3
             CONNECTION 'port=5403 user=postgres dbname=postgres'
             PUBLICATION pub3
             WITH (copy_data = false, origin = none);
node_2=# CREATE SUBSCRIPTION sub2_pub3
             CONNECTION 'port=5403 user=postgres dbname=postgres'
             PUBLICATION pub3
             WITH (copy_data = false, origin = none);

Третий узел подписывается на первые два узла.

node_3=# CREATE SUBSCRIPTION sub3_pub1
             CONNECTION 'port=5401 user=postgres dbname=postgres'
             PUBLICATION pub1
             WITH (copy_data = true, origin = none);

WARNING:  subscription "sub3_pub1" requested copy_data with origin = NONE but might copy data that had a different origin
DETAIL:  Subscribed publication "pub1" is subscribing to other publications.
HINT:  Verify that initial data copied from the publisher tables did not come from other origins.
NOTICE:  created replication slot "sub3_pub1" on publisher
CREATE SUBSCRIPTION

Обратите внимание на параметры подписки: copy_data = true, origin = none. Команда CREATE SUBSCRIPTION выдает предупреждение о том, что с первого узла будут скопированы все данные, в том числе помеченные источником репликации. Но для начальной синхронизации третьего узла это правильно, нам нужно перенести на него обе существующие записи.

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

node_3=# CREATE SUBSCRIPTION sub3_pub2
             CONNECTION 'port=5402 user=postgres dbname=postgres'
             PUBLICATION pub2
             WITH (copy_data = false, origin = none);

Настройка завершена, добавим запись в третьем узле:

node_3=# INSERT INTO test (id) VALUES (1);

Проверяем содержимое на всех узлах.

node_1=# SELECT * FROM test ORDER BY 1,2;

 node   | id 
--------+----
 node_1 |  1
 node_2 |  1
 node_3 |  1
(3 rows)

node_2=# SELECT * FROM test ORDER BY 1,2;

 node   | id 
--------+----
 node_1 |  1
 node_2 |  1
 node_3 |  1
(3 rows)

node_3=# SELECT * FROM test ORDER BY 1,2;

 node   | id 
--------+----
 node_1 |  1
 node_2 |  1
 node_3 |  1
(3 rows)

Привет, встроенный мультимастер!

pg_auth_members: управление предоставлением членства в ролях
commit: ce6b672e, 6566133c

Для демонстрации будем использовать три обычные роли с правом подключения к серверу:

CREATE ROLE alice LOGIN;
CREATE ROLE bob LOGIN;
CREATE ROLE charlie LOGIN;

Сначала посмотрим на поведение в «старой» 15-й версии, а затем перейдем к изменениям в 16-й.

Предоставим alice членство в роли bob. При помощи предложения GRANTED BY команды GRANT можно указать, какая именно роль предоставила это членство, и эта информация сохраняется в системном каталоге:

15=# GRANT bob TO alice GRANTED BY charlie;

15=# SELECT roleid::regrole,
            member::regrole,
            grantor::regrole,
            *
FROM pg_auth_members
WHERE roleid::regrole::text !~ '^pg_';

 roleid | member | grantor | roleid | member | grantor | admin_option
--------+--------+---------+--------+--------+---------+--------------
 bob    | alice  | charlie |  24590 |  24589 |   24591 | f

Но не зная выполненной команды, а просто посмотрев в pg_auth_members, можно задаться вопросом: почему charlie смог предоставить alice членство в роли bob? Ведь для этого charlie сам должен быть членом роли bob, причем с возможностью включения других ролей (WITH ADMIN OPTION). А charlie не включен в роль bob.

Команда GRANT выполнялась от имени суперпользователя (postgres), для которого подобная проверка не выполнялась. Но в результате мы имеем запись в системном каталоге о том, что charlie предоставил alice членство в роли bob, не имея на это прав. Что не правильно.

Идем дальше. Что произойдет, если мы удалим роль charlie?

15=# DROP ROLE charlie;

DROP ROLE

Роль спокойно удаляется. Но что с членством alice в bob, которое предоставил charlie?

15=# SELECT roleid::regrole,
            member::regrole,
            grantor::regrole,
            *
FROM pg_auth_members
WHERE roleid::regrole::text !~ '^pg_';

 roleid | member | grantor | roleid | member | grantor | admin_option
--------+--------+---------+--------+--------+---------+--------------
 bob    | alice  | 24591   |  24590 |  24589 |   24591 | f

На месте не только членство, но и информация о grantor! 24591 это OID удаленной роли charlie. По сути, в таблице pg_auth_members остался мусор. Более того, освободившийся номер OID в будущем может быть заново использован при создании другой роли! И тогда получится, что кто-то еще, а вовсе не charlie, предоставил alice членство в bob. Такое маловероятно, но возможно.

Теперь переходим к изменениям в 16-й версии.

После создания трех ролей попробуем включить alice в bob от имени charlie:

16=# GRANT bob TO alice GRANTED BY charlie;

ERROR:  grantor must have ADMIN OPTION on "bob"

Мы получаем логичную в этой ситуации ошибку о том, что для успешного выполнения команды роль charlie должна быть включена в роль bob с правом включения других ролей.

16=# GRANT bob TO charlie WITH ADMIN OPTION;

GRANT ROLE

16=# GRANT bob TO alice GRANTED BY charlie;

GRANT ROLE

Вот теперь всё в порядке, alice включена в роль bob.

Пробуем удалить роль charlie:

16=# DROP ROLE charlie;

ERROR:  role "charlie" cannot be dropped because some objects depend on it
DETAIL:  privileges for membership of role alice in role bob

Или отозвать ADMIN OPTION:

16=# REVOKE ADMIN OPTION FOR bob FROM charlie ;

ERROR:  dependent privileges exist
HINT:  Use CASCADE to revoke them too.

Отозвать ADMIN OPTION можно, только указав дополнительно CASCADE, а удалить роль нельзя. Роль charlie теперь зависит от факта предоставления членства в роли. Для реализации зависимости в таблицу pg_auth_members добавлен столбец OID, а сама зависимость фиксируется в pg_shdepend. Можем найти её:

16=# SELECT oid,
            roleid::regrole,
            member::regrole,
            grantor::regrole
FROM pg_auth_members
WHERE oid = 16572;

  oid  | roleid | member | grantor
-------+--------+--------+---------
 16572 | bob    | alice  | charlie

16=# SELECT d.classid::regclass,
            (SELECT m FROM pg_auth_members m WHERE m.oid = d.objid) AS objid,
            d.refclassid::regclass,
            d.refobjid::regrole
FROM pg_shdepend d\gx

-[ RECORD 1 ]-----------------------------
classid    | pg_auth_members
objid      | (16572,16569,16568,16570,f,t)
refclassid | pg_authid
refobjid   | charlie

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

pg_auth_members: членство в ролях и наследование привилегий
commit: e3ce2de0, 48a257d4

Предыдущий патч посвящен роли, предоставляющей членство в ролях. Теперь речь непосредственно о роли, получившей членство в другой роли.

Начнем с 15-й версии:

15-postgres=# CREATE ROLE alice LOGIN;
15-postgres=# CREATE ROLE bob LOGIN;

15-postgres=# GRANT bob TO alice;

Мы включаем alice в bob для того, чтобы роль alice могла пользоваться привилегиями роли bob.

15-postgres=# CREATE TABLE t AS SELECT 42 AS col;
15-postgres=# GRANT SELECT ON t TO bob;

Не только bob, но и alice может увидеть содержимое таблицы:

15-alice=> SELECT * FROM t;

 col
-----
  42
(1 row)

Это работает, т. к. у роли alice есть атрибут INHERIT (по умолчанию есть у всех ролей), и привилегии роли bob автоматически наследуются для alice.

15-alice=> SELECT rolname, rolinherit FROM pg_roles WHERE rolname = 'alice';

 rolname | rolinherit
---------+------------
 alice   | t

Если отключить этот атрибут, то alice придется явно переключаться на роль bob для вызова функции:

15-postgres=# ALTER ROLE alice NOINHERIT;

15-alice=> SELECT * FROM t;

ERROR:  permission denied for table t

15-alice=> SET ROLE bob;
15-alice=> SELECT * FROM t;

 col
-----
  42
(1 row)

Предположим, что именно такое поведение и требуется.

Со временем решили предоставить роли alice возможности мониторинга системы. Для этого включаем её в преднастроенную роль pg_read_all_settings для просмотра всех параметров конфигурации.

15-postgres=# GRANT pg_read_all_settings TO alice;

15-alice=> SHOW data_directory;

ERROR:  must be superuser or have privileges of pg_read_all_settings to examine "data_directory"

Отсутствие атрибута INHERIT по-прежнему заставляет явно переключаться на роль с нужными привилегиями:

15-alice=> SET ROLE pg_read_all_settings;
15-alice=> SHOW data_directory;

   data_directory    
---------------------
 /home/pal/pg15/data

Но переключаться в роль pg_read_all_settings явный перебор! Хочется, чтобы привилегии pg_read_all_settings были доступны по умолчанию, а для получения привилегий роли bob требовалось выполнять явное переключение.

Именно эту задачу решает патч. Воспроизведем ситуацию в 16-й версии:

16-postgres=# CREATE ROLE alice LOGIN;
16-postgres=# CREATE ROLE bob LOGIN;

16-postgres=# CREATE TABLE t AS SELECT 42 AS col;
16-postgres=# GRANT SELECT ON t TO bob;

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

16-postgres=# GRANT bob TO alice WITH INHERIT FALSE;
16-postgres=# GRANT pg_read_all_settings TO alice WITH INHERIT TRUE;

Теперь alice может сразу пользоваться привилегиями pg_read_all_settings, а для выполнения функции get_ultimate_answer потребуется переключение в роль bob:

16-alice=> SHOW data_directory;

   data_directory    
---------------------
 /home/pal/pg16/data

16-alice=> SELECT * FROM t;

ERROR:  permission denied for table t

16-alice=> SET ROLE bob;
16-alice=> SELECT * FROM t;

 col
-----
  42
(1 row)

Факт предоставления членства в ролях (GRANT) записывается в pg_auth_members. Соответственно в эту таблицу добавлен признак наследования привилегий (inherit_option):

16-postgres=# SELECT roleid::regrole, member::regrole, grantor::regrole,
                     admin_option, inherit_option
FROM pg_auth_members
WHERE member = 'alice'::regrole;

        roleid        | member | grantor  | admin_option | inherit_option
----------------------+--------+----------+--------------+----------------
 bob                  | alice  | postgres | f            | f
 pg_read_all_settings | alice  | postgres | f            | t

А что же стало с атрибутом роли INHERIT? Он теперь используется только в команде GRANT, если в ней явно не указано WITH INHERIT. В нашем примере у alice установлен атрибут INHERIT, значит следующая команда установит его в pg_auth_members:

16-postgres=# GRANT pg_read_all_data TO alice;

Значение по умолчанию изменится, если атрибут отозвать.

16-postgres=# ALTER ROLE alice NOINHERIT;
16-postgres=# GRANT pg_write_all_data TO alice;

16-postgres=# SELECT roleid::regrole, member::regrole, grantor::regrole,
                     admin_option, inherit_option
FROM pg_auth_members
WHERE member = 'alice'::regrole;

        roleid        | member | grantor  | admin_option | inherit_option
----------------------+--------+----------+--------------+----------------
 bob                  | alice  | postgres | f            | f
 pg_read_all_settings | alice  | postgres | f            | t
 pg_read_all_data     | alice  | postgres | f            | t
 pg_write_all_data    | alice  | postgres | f            | f

Важный вывод. Поведение сервера в части наследования привилегий в 16-й версии изменится. Атрибут роли INHERIT больше не влияет на наследование привилегий, а влияет только на последующие команды GRANT без указания WITH INHERIT.

Второй коммит касается команды ALTER DEFAULT PRIVILEGES. Управлять привилегиями других ролей теперь можно только при наличии атрибута INHERIT. В нашем примере alice не может использовать ALTER DEFAULT PRIVILEGES для управления привилегиями объектов, которыми владеет bob.

pg_receivewal, pg_recvlogical: обработка сигнала SIGTERM
commit: 8b60db77

pg_receivewal и pg_recvlogical можно запускать как демонов. В таком случае для их остановки systemd умеет отправлять только сигнал SIGTERM, но обе утилиты для корректной остановки понимают только SIGINT. А по SIGTERM просто прекращают работу. И всё бы ничего, но pg_receivewal с включенным сжатием сбрасывает файлы на диск только при корректном завершении.

В итоге для обеих утилит добавили обработку сигнала SIGTERM.

---

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