Август в релизном цикле PostgreSQL месяц особенный. Еще не вышла официально 15-я версия, но уже закончился первый коммитфест 16-й версии. И мы можем посмотреть на самые интересные изменения.

Собираем сервер из исходного кода и вперед!

\dconfig server_version

List of configuration parameters
   Parameter    |  Value  
----------------+---------
 server_version | 16devel

PostgreSQL 15

После заморозки кода 15-й версии не обошлось без потерь. Откатили:

Индексы BRIN не блокируют HOT-обновления
Оптимизация работы с вложенными транзакциями

PostgreSQL 16

В этот обзор попали 14 изменений:

psql: \pset xheader_width
vacuumdb --schema и --exclude-schema
Новые возможности утилиты createuser
Сообщение журнала о контрольной точке дополнили номерами LSN самой точки и начала восстановления
pg_prepared_statements.result_types
auto_explain: параметр log_parameter_max_length
Необязательные псевдонимы для подзапросов во фразе FROM
REINDEX: синтаксис и не только
CREATE STATISTICS: необязательно указывать имя статистики
CREATE TABLE: атрибут STORAGE
У созданного при инициализации кластера пользователя нельзя отобрать полномочия суперпользователя
Триггеры на TRUNCATE для внешних таблиц
pg_read_file/pg_read_binary_file: новая вариация указания параметров
Собственные менеджеры ресурсов WAL

psql: \pset xheader_width
commit: a45388d6

Развернутый режим вывода результатов запросов (\x или \pset expanded) удобен когда данные всех столбцов не помещаются на экран. Но и в развернутом режиме данные длинных столбцов также могут не помещаться в окно терминала.

Для примера в окне терминала шириной в 72 символа выполним запрос:

\pset expanded on
\pset pager off

SELECT version(),
       length(version()) version_length;

-[ RECORD 1 ]--+--------------------------------------------------------
-------------------------------------------------
version        | PostgreSQL 16devel on x86_64-pc-linux-gnu, compiled by 
gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit
version_length | 104

Значение функции version не помещается по ширине, поэтому переносится на следующую строку. С этим все нормально. Но вот строка заголовка записи форматируется по самому широкому столбцу и также переносится на следующую строку. И чем длиннее самый широкий столбец, тем больше места будет занимать строка заголовка записи. Это особенно неудобно, когда выводится несколько записей и у каждой записи заголовок занимает несколько строк. (Попробуйте выполнить в развернутом режиме запрос SELECT * FROM pg_proc;)

В 16-й версии в psql появился новый параметр форматирования строки заголовка развернутого режима.

\pset xheader_width

Expanded header width is 'full'.

Значение по умолчанию (full) соответствует поведению в предыдущих версиях. Другие возможные значения:

column ― заголовок записи обрезается до ширины первого столбца вывода;
page ― заголовок записи обрезается до ширины окна терминала;
число ― заголовок записи обрезается до указанного числа.

\pset xheader_width column

Expanded header width is 'column'.

SELECT version(),
       length(version()) version_length;

-[ RECORD 1 ]--+
version        | PostgreSQL 16devel on x86_64-pc-linux-gnu, compiled by 
gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit
version_length | 104

Заголовок записи больше не расходует напрасно полезное место окна терминала.

vacuumdb --schema и --exclude-schema
commit: 7781f4e3

У vacuumdb новые параметры для очистки таблиц в указанных схемах (--schema) или таблиц, не принадлежащих указанным схемам (--exclude-schema).

Новые возможности утилиты createuser
commit: 08951a7c

Утилита createuser является оберткой над командой CREATE ROLE, но с ограниченным функционалом. Это изменение добавляет утилите ряд параметров, реализующих отсутствующие ранее возможности, а именно:

--valid-until для формирования фразы VALID UNTIL;
--bypassrls/--no-bypassrls для формирования атрибута BYPASSRLS/NOBYPASSRLS;
-m/--member для включения создаваемой роли в указанную роль;
-a/--admin для включения создаваемой роли в указанную роль с предложением WITH ADMIN OPTION.

Сообщение журнала о контрольной точке дополнили номерами LSN самой точки и начала восстановления
commit: 62c46eee

После завершения контрольной точки в WAL делается запись с номером LSN контрольной точки и номером LSN, откуда начинать восстановление в случае сбоя (redo LSN).

Для последней выполненной контрольной точки эти два номера LSN записываются в управляющий файл:

$ pg_controldata | egrep 'Latest.*location'

Latest checkpoint location:         1/1A3DEB20
Latest checkpoint's REDO location:  1/1A3DEAE8

А в 16-й версии их добавили в сообщение о завершении контрольной точки журнала сервера:

2022-08-02 12:15:17.961 MSK [198868] LOG:  checkpoint complete: wrote 0 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.001 s, sync=0.001 s, total=0.072 s; sync files=0, longest=0.000 s, average=0.000 s; distance=0 kB, estimate=418462 kB; lsn=1/1A3DEB20, redo lsn=1/1A3DEAE8

Стоит напомнить, что начиная с 15-й версии параметр log_checkpoints включен по умолчанию.

pg_prepared_statements.result_types
commit: 84ad713c, 6ffff0fd

В представление pg_prepared_statements добавили столбец result_types.

PREPARE get_booking (text) AS
SELECT * FROM bookings WHERE book_ref = $1;

SELECT * FROM pg_prepared_statements WHERE name = 'get_booking'\gx

-[ RECORD 1 ]---+-----------------------------------------------
name            | get_booking
statement       | PREPARE get_booking (text) AS                 +
                | SELECT * FROM bookings WHERE book_ref = $1;
prepare_time    | 2022-08-02 16:54:14.313221+03
parameter_types | {text}
result_types    | {character,"timestamp with time zone",numeric}
from_sql        | t
generic_plans   | 0
custom_plans    | 0

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

auto_explain: параметр log_parameter_max_length
commit: d4bfe412

Новый параметр расширения auto_explain.log_parameter_max_length выполняет ту же задачу, что и аналогичный log_parameter_max_length, а именно ― журналирует значения параметров запросов.

LOAD 'auto_explain';
SHOW auto_explain.log_parameter_max_length;

 auto_explain.log_parameter_max_length
---------------------------------------
 -1
(1 row)

Значение по умолчанию равно -1. Это говорит о том, что будут выводиться полные значения параметров. Значение 0 отключает вывод значений параметров, положительное число ограничивает вывод значений указанным количеством байт.

Настроим журналирование и сформируем параметризированный запрос командой EXECUTE… USING в анонимном блоке PL/pgSQL.

SET auto_explain.log_min_duration = 0;
SET auto_explain.log_nested_statements = 'on';
SET auto_explain.log_level = 'NOTICE';

DO $$BEGIN EXECUTE 'SELECT $1' USING 42; END;$$;

NOTICE:  duration: 0.003 ms  plan:
Query Text: SELECT $1
Query Parameters: $1 = '42'
Result  (cost=0.00..0.01 rows=1 width=4)
DO

В вывод добавилась строка Query Parameters, позволяющая узнать, с какими значениями выполнялся запрос в строке Query Text.

Необязательные псевдонимы для подзапросов во фразе FROM
commit: bcedd8f5

Для подзапросов во фразе FROM стандарт SQL требует наличия псевдонима:

15=# SELECT * FROM (SELECT 42 AS a);

ERROR:  subquery in FROM must have an alias
LINE 1: SELECT * FROM (SELECT 42 AS a);
                    ^
HINT:  For example, FROM (SELECT ...) [AS] foo.

Однако это осложняет миграцию с других СУБД, где наличие псевдонима необязательно.

В 16-й версии такие псевдонимы не обязательны и в PostgreSQL.

16=# SELECT * FROM (SELECT 42 AS a);

 a  
----
 42
(1 row)

REINDEX: синтаксис и не только
commit: 2cbc3c17

Варианты команды REINDEX DATABASE и REINDEX SYSTEM требовали указания базы данных. Но указать можно только текущую базу данных, что делает это требование излишним. В новой версии имя базы данных становится необязательным.

Кроме того, команда REINDEX DATABASE теперь переиндексирует только пользовательские индексы и пропускает индексы таблиц системного каталога. Прежнего поведения, а именно перестроения всех индексов базы данных, можно добиться выполнением двух команд REINDEX DATABASE и REINDEX SYSTEM.

Такое изменение обосновано тем, что до сих пор не было способа перестроить только пользовательские индексы во всей базе данных. К тому же перестроение системных индексов невозможно в режиме CONCURRENTLY, а в высоконагруженных системах может вызывать взаимоблокировки.

CREATE STATISTICS: необязательно указывать имя статистики
commit: 624aa2a1

Расширенная статистика ― отдельный объект в базе данных и нуждается в имени. Но почему бы не разрешить опускать имя при создании, доверив его формирование самой СУБД? Точно так же, как это возможно для индексов и ограничений целостности.

Сделали:

CREATE STATISTICS ON departure_airport, arrival_airport FROM flights;

\d flights

...
Statistics objects:
    "bookings.flights_departure_airport_arrival_airport_stat" ON departure_airport, arrival_airport FROM flights

CREATE TABLE: атрибут STORAGE
commit: 784cedda

Предположим, мы решили хранить изображения в базе данных. Создаем таблицу:

CREATE TABLE images (filename text, data bytea);
ALTER TABLE images ALTER COLUMN data SET STORAGE external;

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

Вопрос в том, зачем это делать отдельной командой, а не сразу указать нужную стратегию в CREATE TABLE? Ответ простой: CREATE TABLE до 16-й версии не позволяет указывать атрибут STORAGE для столбцов, что и было исправлено:

CREATE TABLE images (filename text, data bytea STORAGE external);

У созданного при инициализации кластера пользователя нельзя отобрать полномочия суперпользователя
commit: e530be2c

Суперпользователь, созданный при инициализации кластера (pg_authid.oid=10), мог сам у себя отобрать атрибут SUPERUSER:

15=# ALTER ROLE postgres NOSUPERUSER;

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

В 16-й версии случайно лишиться суперпользовательских полномочий не получится:

16=# ALTER ROLE postgres NOSUPERUSER;

ERROR:  permission denied: bootstrap user must be superuser

Кстати, удалить суперпользователя, созданного при инициализации кластера было и раньше нельзя:

CREATE ROLE root LOGIN SUPERUSER;
\c - root

DROP ROLE postgres;

ERROR:  cannot drop role postgres because it is required by the database system

Общий вывод можно сделать такой. Начальный суперпользователь необходим для нормального функционирования сервера. Удалять или отнимать у него привилегии не нужно. В плане обеспечения дополнительной безопасности можно подумать о том, чтобы сделать эту роль групповой, забрав атрибут LOGIN.

Триггеры на TRUNCATE для внешних таблиц
commit: 3b00a944

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

pg_read_file/pg_read_binary_file: новая вариация указания параметров
commit: 283129e3

Спецификация функций pg_read_file и похожей pg_read_binary_file была такой:

pg_read_file ( filename text [, offset bigint, length bigint [, missing_ok boolean ]] ) → text

Предполагается, что для чтения файла целиком нужно не указывать параметры offset и length. Но тогда не получается задать missing_ok=true для игнорирования отсутствия файла.

В 16-й версии добавили еще один перегруженный вариант функций, что привело общую спецификацию к виду:

pg_read_file ( filename text [, offset bigint, length bigint ] [, missing_ok boolean ] ) → text

Теперь можно указать только имя файла и игнорировать его отсутствие.

Собственные менеджеры ресурсов WAL
commit: 5c279a6d

Особую роль патча подчеркивает мой коллега, Егор Рогов. Ему и слово.

Одна из проблем, стоящих перед разработчиками табличных и индексных методов доступа — невозможность формировать журнальные записи специфического вида. Существует механизм унифицированных журнальных записей, который сбрасывает в WAL “разницу” между старым и новым состояниями страницы (им пользуется, например, расширение rum), но он не слишком эффективен и не поддерживает логическую репликацию.

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

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

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

PostgreSQL 16: Часть 1 или Коммитфест 2022-07