PostgreSQL 16: Часть 5 или Коммитфест 2023-0303.05.2023 08:16

736b4bc224282579ed277a55dcb55108

Вместе с окончанием мартовского коммитфеста, закончился прием изменений в 16-ю версию. Пришла пора посмотреть, что в нем было нового и интересного.

Надеюсь, что представленный материал вместе с предыдущими статьями серии (2022–07, 2022–09, 2022–11, 2023–01) поможет сформировать представление о новинках PostgreSQL 16.

В мартовском коммитфесте традиционно много разработок. Для удобства просмотра они сгруппированы по разделам:


Мониторинг

pg_stat_io: статистика ввода/вывода
Счетчик новых версий строк, перенесенных на другую страницу при выполнении UPDATE
pg_buffercache: новая функция pg_buffercache_usage_counts
Нормализация DDL и служебных команд, продолжение
EXPLAIN (generic_plan): общий план параметризированного запроса
auto_explain: протоколирование идентификатора запроса
PL/pgSQL: GET DIAGNOSTICS… PG_ROUTINE_OID


Клиентские приложения

psql: переменные SHELL_ERROR и SHELL_EXIT_CODE
psql: \watch и количество повторений
psql: \df+ не показывает исходный код функций
pg_dump: поддержка методов сжатия LZ4 и zstd
pg_dump и секционированные таблицы
pg_verifybackup --progress
libpq: балансировка подключений


Администрирование и сопровождение сервера

initdb: установка параметров конфигурации при инициализации кластера
Автоочистка: регулирование нагрузки на лету
Управление размером общей памяти для очистки и анализа
VACUUM только для TOAST-таблицы
Удален параметр vacuum_defer_cleanup_age
pg_walinspect: трактовка параметра end_lsn
pg_walinspect: pg_get_wal_fpi_info → pg_get_wal_block_info


Локализация

ICU: правило сортировки UNICODE
ICU: канонизация локалей
ICU: пользовательские правила для адаптации алгоритма сортировки


Безопасность

libpq: новый параметр require_auth
scram_iterations: счетчик итераций для шифрования паролей с использованием SCRAM-SHA-256


Функции и команды SQL

Поддержка стандарта SQL/JSON
Новые функции pg_input_error_info и pg_input_is_valid
Фонетический алгоритм Daitch-Mokotoff Soundex
Новые функции array_shuffle и array_sample
Новая агрегатная функция any_value
COPY: загрузка значений по умолчанию
timestamptz: добавление и вычитание интервалов времени
XML: форматирование значений
pg_size_bytes: поддержка «B»
Новые функции: erf, erfc


Производительность

Параллельное выполнение полного и правого хеш-соединения
Варианты реализации правого антисоединения
Переработан механизм расширения отношений
Индексы BRIN не блокируют HOT-обновления
postgres_fdw: прерывание транзакций на удаленных серверах в параллельном режиме
force_parallel_mode → debug_parallel_query
Прямой ввод/вывод (только для разработчиков)


Логическая репликация

Логическая репликация с физической реплики
Использование неуникальных индексов с REPLICA IDENTITY FULL
Начальная синхронизация в двоичном формате
Права на создание подписки и применение изменений
Параллельное применение изменений (только для разработчиков)


Мониторинг

pg_stat_io: статистика ввода/вывода
commit: a9c70b46, ac8d53da, 8aaa04b3

Новое представление pg_stat_io показывает статистику ввода/вывода.

Сразу оговоримся, что речь о дисковом вводе/выводе с точки зрения PostgreSQL. Кеширование на более низком уровне (ОС, контроллер диска) в нем не учитывается. Кроме того, в настоящий момент учитываются только операции с буферным кешем. Перенос таблиц/индексов в другое табличное пространство, запись WAL ― могут быть добавлены позже.

Каждая строка представления идентифицируется тремя столбцами:


  • backend_type ― тип процесса, знакомый по pg_stat_activity.backend_type;
  • object ― тип объектов: relation (постоянные таблицы, индексы, …) или temp relation;
  • context ― тип операции над объектом: normal, vacuum, bulkread, bulkwrite.

Названия операций интуитивно понятные, за точным описанием стоит заглянуть в документацию на pg_stat_io.

Посмотрим на одну из строк: 

SELECT *
FROM pg_stat_io
WHERE backend_type = 'client backend' AND
      object = 'relation' AND
      context = 'bulkread'
\gx
-[ RECORD 1 ]+------------------------------
backend_type | client backend
object       | relation
context      | bulkread
reads        | 427497
read_time    | 752.489
writes       | 77220
write_time   | 215.93
extends      |
extend_time  |
op_bytes     | 8192
hits         | 38683
evictions    | 94210
reuses       | 330437
fsyncs       |
fsync_time   |
stats_reset  | 2023-04-29 09:13:58.798952+03

В этой строке показана накопительная статистика всех клиентских процессов (client backend) выполнявших массовое чтение (bulkread) из постоянных отношений (relation). К таким операциям относится последовательное сканирование больших таблиц с использованием буферного кольца.

Видна статистика по количеству попаданий (hits) в буферный кеш, вытеснений (evictions) из кеша, повторных использований в кольцевом буфере (reuses). Также видно, что клиентским процессам приходилось большую часть вытесняемых буферов записывать на диск (writes).

Столбцы reads, writes, extends (расширение файлов отношений) показывают количество выполненных операций. Для перевода в единицы памяти, значения нужно умножить на op_bytes. Поскольку сейчас учитываются только операции с буферным кешем, то op_bytes всегда равен размеру страницы ― 8 КБ. Для сбора статистики времени выполнения (столбцы *_time) должен быть включен параметр track_io_timing.

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

SELECT backend_type, SUM(writes) blocks,
       pg_size_pretty(SUM(writes*op_bytes)) size,
       round(SUM(write_time)) "time, ms"
FROM pg_stat_io
WHERE writes > 0
GROUP BY ROLLUP (backend_type)
ORDER BY blocks;
   backend_type    | blocks |  size   | time, ms
-------------------+--------+---------+----------
 background writer |  17198 | 134 MB  |      187
 checkpointer      |  30436 | 238 MB  |      139
 background worker |  76929 | 601 MB  |      213
 autovacuum worker |  88870 | 694 MB  |      528
 client backend    | 369031 | 2883 MB |     1055
                   | 582464 | 4551 MB |     2122
(6 rows)

Два главных процесса записи на диск background writer и checkpointer сделали меньше всех. Явный признак, что конфигурация системы не идеальна: возможно стоит увеличить буферный кеш и/или настроить background writer на более агрессивную работу.

Похожую информацию можно получить из представления pg_stat_bgwriter. Столбцы buffers_clean и buffers_checkpoint покажут сколько буферов на диск записали процессы background writer и checkpointer. Однако столбец buffers_backend крайне не информативен. В нем собираются результаты не только клиентских процессов, но и прочих, в частности процессов автоочистки. Кроме того, операции расширения файлов отношений не учитываются отдельно (как в pg_stat_io), а также приплюсовываются к buffers_backend, хотя это не запись из буферного кеша на диск.

См. также
Waiting for PostgreSQL 16 — Add pg_stat_io view, providing more detailed IO statistics (Hubert 'depesz' Lubaczewski)
Waiting for Postgres 16: Cumulative I/O statistics with pg_stat_io (Lukas Fittl)

Счетчик новых версий строк, перенесенных на другую страницу при выполнении UPDATE
commit: ae4fdde1

В семействе статистических представлений pg_stat_*_tables появился новый столбец — n_tup_newpage_upd. Вместе с существующими столбцами n_tup_hot_upd и n_tup_upd он позволяет оценить эффективность оптимизации HOT.

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

Если значение счетчика достаточно велико, то это повод задуматься об уменьшении значения fillfactor для таблицы. Резервируя место на странице, мы увеличиваем шансы на срабатывание оптимизации HOT.

В отдельной базе данных подготовим pgbench к работе: 

=# CREATE DATABASE test_fillfactor;
=# \c test_fillfactor
$ pgbench -i test_fillfactor

Запускаем тест на 10 секунд и смотрим статистику изменений строк таблицы pgbench_accounts: 

$ pgbench -T 10 test_fillfactor
SELECT relname, n_tup_upd, n_tup_hot_upd, n_tup_newpage_upd
FROM pg_stat_all_tables
WHERE relname = 'pgbench_accounts'\gx
-[ RECORD 1 ]-----+-----------------
relname           | pgbench_accounts
n_tup_upd         | 11307
n_tup_hot_upd     | 9644
n_tup_newpage_upd | 1663

Сумма n_tup_hot_upd и n_tup_newpage_upd равняется n_tup_upd. Это говорит о том, что в тесте все команды UPDATE не обновляют индексированные столбцы и могут использовать оптимизацию HOT. Но примерно в 15% случаев оптимизация HOT не сработала из-за того, что на странице не хватило места для новой версии строки.

Уменьшим fillfactor и повторим тест, предварительно сбросив накопленную статистику: 

ALTER TABLE pgbench_accounts SET (fillfactor = 80);
SELECT pg_stat_reset_single_table_counters('pgbench_accounts'::regclass);
$ pgbench -T 10 test_fillfactor
SELECT relname, n_tup_upd, n_tup_hot_upd, n_tup_newpage_upd
FROM pg_stat_all_tables
WHERE relname = 'pgbench_accounts'\gx
-[ RECORD 1 ]-----+-----------------
relname           | pgbench_accounts
n_tup_upd         | 11707
n_tup_hot_upd     | 11704
n_tup_newpage_upd | 3

Ситуация радикально изменилась. Только трижды не хватило места на странице для новой версии строки.

Любопытно, что кандидатом на изменение fillfactor является системная таблица pg_statistic: 

VACUUM ANALYZE;

SELECT relname, n_tup_upd, n_tup_hot_upd, n_tup_newpage_upd
FROM pg_stat_all_tables
WHERE relname = 'pg_statistic'\gx
-[ RECORD 1 ]-----+-------------
relname           | pg_statistic
n_tup_upd         | 458
n_tup_hot_upd     | 182
n_tup_newpage_upd | 276

pg_buffercache: новая функция pg_buffercache_usage_counts
commit: f3fa3132

В 16-й версии расширение pg_buffercache пополнилось функцией pg_buffercache_summary, она упоминалась в статье о ноябрьском коммитфесте.

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

SELECT * FROM pg_buffercache_usage_counts();
 usage_count | buffers | dirty | pinned
-------------+---------+-------+--------
           0 |   15791 |     0 |      0
           1 |     105 |     1 |      0
           2 |      89 |     4 |      0
           3 |      22 |     1 |      0
           4 |      32 |     3 |      0
           5 |     345 |    27 |      0
(6 rows)

Основное преимущество pg_buffercache_summary и pg_buffercache_usage_counts перед представлением pg_buffercache в скорости работы. Функции не требуют блокировки буферов, поэтому выполняются значительно быстрее.

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

Это продолжение работы по нормализации запросов в 16-й версии.

На этот раз изменения коснулись команд: DECLARE, EXPLAIN, CREATE MATERIALIZED VIEW и CREATE TABLE AS. Но что особенно важно, нормализация учитывает константы в тексте запросов и заменяет их на параметры.

Создадим три временные таблицы с константой во фразе AS SELECT: 

SELECT format('create temp table tmp on commit drop as select %s', g.i)
FROM generate_series(1,3) as g(i)\gexec
SELECT 1
SELECT 1
SELECT 1

А теперь убедимся, что в pg_stat_statements все три команды считаются одной: 

SELECT queryid, query, calls                                                                               
FROM pg_stat_statements
WHERE query ILIKE 'create temp table%';
       queryid       |                       query                       | calls
---------------------+---------------------------------------------------+-------
 2417319575362882285 | create temp table tmp on commit drop as select $1 |     3
(1 row)

См. также
Postgres 16 highlight — Normalization of utilities in pg_stat_statements (Michael Paquier)

EXPLAIN (generic_plan): общий план параметризированного запроса
commit: 3c05284d

Идея этой разработки следующая.

Долгие запросы записываются в журнал сервера для последующего анализа.

SHOW log_min_duration_statement;
 log_min_duration_statement
----------------------------
 100ms

Приложение использует расширенный протокол запросов и отправляет на сервер запросы с параметрами. Для их имитации воспользуемся новой в 16-й версии командой \bind в psql: 

\bind ABCDEF
SELECT count(*) FROM tickets WHERE book_ref = $1;
 count
-------
     0
(1 row)

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

$ tail -2 logfile
LOG:  duration: 172.195 ms  execute : SELECT count(*) FROM tickets WHERE book_ref = $1;
DETAIL:  parameters: $1 = 'ABCDEF'

Теперь стоит задача разобраться, почему запрос работает долго. Для этого нужно увидеть план запроса. Но простая попытка добавить слово EXPLAIN к тексту запроса завершается ошибкой: 

EXPLAIN SELECT count(*) FROM tickets WHERE book_ref = $1;
ERROR:  there is no parameter $1
LINE 1: EXPLAIN SELECT count(*) FROM tickets WHERE book_ref = $1;
                                                              ^

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

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

Но для анализа может помочь и общий план запроса (generic plan), не зависящий от значений параметров. Теперь его можно получить: 

EXPLAIN (generic_plan)
SELECT count(*) FROM tickets WHERE book_ref = $1;
                                   QUERY PLAN                                   
--------------------------------------------------------------------------------
 Aggregate  (cost=65779.07..65779.08 rows=1 width=8)
   ->  Gather  (cost=1000.00..65779.07 rows=2 width=0)
         Workers Planned: 2
         ->  Parallel Seq Scan on tickets  (cost=0.00..64778.87 rows=1 width=0)
               Filter: (book_ref = $1)

Запланировано последовательное сканирование таблицы несколькими процессами. Значит, таблица большого размера. А выбрана будет всего одна запись. Скорее всего по столбцу book_ref нет индекса, который бы ускорил запрос.

См. также
EXPLAIN (GENERIC_PLAN): New in PostgreSQL 16 (Laurenz Albe)

auto_explain: протоколирование идентификатора запроса
commit: 9d2d9728

Согласно документации, протоколирование запросов с включенным параметром auto_explain.log_verbose эквивалентно выводу EXPLAIN с параметром VERBOSE. Однако идентификатор запроса не выводился, что и было исправлено: 

SET compute_query_id = on;

LOAD 'auto_explain';
SET auto_explain.log_min_duration = 0;
SET auto_explain.log_verbose = on;
SET auto_explain.log_level = 'NOTICE';

SELECT 1;
NOTICE:  duration: 0.009 ms  plan:
Query Text: SELECT 1;
Result  (cost=0.00..0.01 rows=1 width=4)
  Output: 1
Query Identifier: -7037075969593950510
 ?column?
----------
        1
(1 row)

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

PL/pgSQL: GET DIAGNOSTICS… PG_ROUTINE_OID
commit: d3d53f95

В коде функции можно получить ее идентификатор: 

CREATE FUNCTION f (OUT func_oid oid)
AS 'BEGIN GET DIAGNOSTICS func_oid = PG_ROUTINE_OID; END;'
LANGUAGE plpgsql;

SELECT f() oid, f()::regprocedure signature;
  oid  | signature
-------+-----------
 16528 | f()


Клиентские приложения

psql: переменные SHELL_ERROR и SHELL_EXIT_CODE
commit: b0d8f2d9, 31ae2aa9

Две новые переменные подскажут, как завершилась последняя команда ОС: 

16=# \! pwd
/home/pal/pg16
16=# \echo SHELL_ERROR: :SHELL_ERROR, SHELL_EXIT_CODE: :SHELL_EXIT_CODE
SHELL_ERROR: false, SHELL_EXIT_CODE: 0

Первый коммит устанавливает эти переменные для команды! и команд в обратных кавычках (``). Второй коммит для команд \g, \o, \w и \copy.

В ряде случаев переменные сознательно не устанавливаются. К ним относятся: вызов программы постраничника, вызов редактора (\e) и установка приглашения командой в обратных кавычках (\set PROMPT1%`команда`).

psql: \watch и количество повторений
commit: 00beecfe

Команда \watch выполняет запрос до тех пор пока не будет прервана. Теперь количество повторений можно указать: 

16=# SELECT 1 \watch interval=1 count=2
Fri 14 Apr 2023 02:47:28 PM MSK (every 1s)

 ?column?
----------
        1
(1 row)

Fri 14 Apr 2023 02:47:29 PM MSK (every 1s)

 ?column?
----------
        1
(1 row)

16=#

psql: \df+ не показывает исходный код функций
commit: 3dfae91f

Команда \df+ среди прочего выводит исходный код функций, который может занимать много места. Это затрудняет просмотр списка функций.

В 16-й версии \df+ больше не будет показывать исходный код всех функций, кроме внутренних. Код внутренней функции представляет собой название функции на языке C и всегда помещается в одну строку.

А для просмотра исходного кода функций в psql есть другие команды: \sf и \ef.

pg_dump: поддержка методов сжатия LZ4 и zstd
commit: 0da243fe, 84adc8e2

До 16-й версии pg_dump поддерживал один метод сжатия ― gzip. Первый коммит добавляет поддержку LZ4, второй ― zstd.

Сжатие резервных копий возможно для всех форматов, кроме tar.

См. также
LZ4 and ZSTD pg_dump compression in PostgreSQL 16 (Pavlo Golub)

pg_dump и секционированные таблицы
commit: a563c24c

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

В 16-й версии в pg_dump благодаря новому параметру --table-and-children перечислять секции или подбирать шаблон не понадобится. Параметр действует не только для секционированных таблиц, но и для таблиц с наследованием.

Для аналогичных целей, но уже при исключении таблиц, добавлены параметры: --exclude-table-and-children и --exclude-table-data-and-children.

pg_verifybackup --progress
commit: d07c2948

Утилита pg_basebackup с параметром --progress показывает прогресс создания резервной копии.

Этот же параметр добавлен утилите проверки резервной копии pg_verifybackup. Во время проверки статус обновляется каждую секунду, а по завершении мы увидим примерно следующее: 

$ pg_verifybackup --progress /home/pal/pg16/backup/
2723991/2723991 kB (100%) verified
backup successfully verified

libpq: балансировка подключений
commit: 7f5b1981

Возможность указать при подключении несколько экземпляров PostgreSQL была и раньше. Если есть несколько реплик, то в строке подключения их можно перечислить в параметрах host, hostaddr и port. Клиенты будут пытаться подключаться к репликам в заданном порядке.

psql "host=replica1,replica2,replica3"

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

Для равномерного распределения подключений между репликами можно на стороне приложения при формировании строки подключения перемешивать список реплик. А можно воспользоваться новым параметром подключения load_balance_hosts: 

psql "host=replica1,replica2,replica3 load_balance_hosts=random"

Значение random говорит о том, что перед подключением список узлов будет перемешан.


Администрирование и сопровождение сервера

initdb: установка параметров конфигурации при инициализации кластера
commit: 3e51b278

Новый параметр утилиты initdb -с или --set позволяет переопределить значение любого параметра конфигурации.

Для экспериментов с логической репликацией создадим второй экземпляр сервера на свободном порту и с параметром wal_level равным logical: 

$ initdb -k -U postgres "-c wal_level=logical" "-c port=5402" -D ~/pg16/data2
$ pg_ctl start -D ~/pg16/data2 -l logfile2
$ psql -p 5402 -c "SHOW wal_level"
 wal_level
-----------
 logical

Указанные при инициализации параметры записываются в конец postgresql.conf: 

$ tail -2 ~/pg16/data2/postgresql.conf
 wal_level = logical
 port = 5402

Автоочистка: регулирование нагрузки на лету
commit: 7d71d3dd

Если автоочистка большой таблицы длится очень долго, то ее можно ускорить прямо на лету. Для этого нужно изменить параметры autovacuum_vacuum_cost_limit и/или autovacuum_vacuum_cost_delay и обновить конфигурацию.

В предыдущих версиях процессы автоочистки перечитывали конфигурационные файлы только после завершения обработки очередной таблицы. А теперь это делается перед каждой проверкой на паузу в работе (autovacuum_vacuum_cost_delay).

См. также
Updating the Cost Limit On The Fly (Robert Haas)

Управление размером общей памяти для очистки и анализа
commit: 1cbbee03, 4830f102

Чтобы не забить общую память, очистка таблицы выполняется в специальной области памяти ― кольцевом буфере. Размер кольцевого буфера невелик и составляет 256 КБ. Однако при очистке таблицы большого размера может пострадать производительность работы всего сервера. Для переиспользования страниц в небольшом кольцевом буфере приходится постоянно записывать на диск грязные страницы и WAL. Частая синхронизация WAL с диском может стать узким местом в системе ввода/вывода.

Теперь размером этой области памяти можно управлять через новый параметр vacuum_buffer_usage_limit. Значение по умолчанию прежнее ― 256 КБ, а допустимые значения находятся в диапазоне от 128 КБ до 16 ГБ (но используется не более ⅛ буферного кеша).

При ручном запуске команд VACUUM и ANALYZE размер можно переопределить в новом параметре команд BUFFER_USAGE_LIMIT. При значении 0 очистка сможет занять весь буферный кеш: 

VACUUM(ANALYZE, BUFFER_USAGE_LIMIT 0);

SELECT buffers_used, buffers_unused FROM pg_buffercache_summary();
 buffers_used | buffers_unused
--------------+----------------
        16384 |              0

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

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

Для описания принципа работы «кольцевого буфера» в глоссарии появился термин «стратегия доступа к буферам» (Buffer Access Strategy).

VACUUM только для TOAST-таблицы
commit: 4211fbd8

Чтобы выполнить очистку только TOAST-таблицы, нужно иметь привилегию USAGE на схему pg_toast и предварительно определить имя таблицы: 

SELECT reltoastrelid::regclass FROM pg_class WHERE oid = 'tickets'::regclass;
      reltoastrelid      
-------------------------
 pg_toast.pg_toast_16443
VACUUM pg_toast.pg_toast_16443;

С отключенным новым параметром PROCESS_MAIN вычислять имя не обязательно: 

VACUUM (PROCESS_MAIN false) tickets;

Утилита vacuumdb получила соответствующий параметр --no-process-main.

Удален параметр vacuum_defer_cleanup_age
commit: be504a3e, 1118cd37

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

Писать полноценные тесты и портировать их в предыдущие версии ― задача сложная и возможно не нужная. Ведь вероятно параметр _vacuum_defer_cleanupage применяется редко. Вместо него удобнее использовать появившиеся позже hot_standby_feedback и слоты репликации.

Поэтому второй коммит удаляет параметр vacuum_defer_cleanup_age. Пользоваться им всё равно неудобно: большое значение будет приводить к излишнему разрастанию таблиц, а маленькое не поможет предотвратить конфликты применения WAL на реплике.

Даже без перехода на 16-ю версию есть повод проверить vacuum_defer_cleanup_age в конфигурационных файлах и запланировать отказ от него.

pg_walinspect: трактовка параметра end_lsn
commit: 5c1b6628

Для функций pg_get_wal_records_info, pg_get_wal_stats, pg_get_wal_block_info изменена трактовка параметра end_lsn. Если значение параметра не указано, то функции выдают информацию от start_lsn и до конца журнала предзаписи.

Это позволило удалить дублирующие функции pg_get_wal_records_info_till_end_of_wal и pg_get_wal_stats_till_end_of_wal.

pg_walinspect: pg_get_wal_fpi_info → pg_get_wal_block_info
commit: 9ecb134a

В предыдущей статье серии упоминалась новая функция pg_get_wal_fpi_info расширения pg_walinspect. Функция существенно переработана и переименована в pg_get_wal_block_info.


Локализация

ICU: правило сортировки UNICODE
commit: 0d21d4b9

В соответствии со стандартом SQL в 16-й версии добавлено правило сортировки UNICODE для провайдера ICU.

\dO unicode
                                     List of collations
   Schema   |  Name   | Provider | Collate | Ctype | ICU Locale | ICU Rules | Deterministic?
------------+---------+----------+---------+-------+------------+-----------+----------------
 pg_catalog | unicode | icu      |         |       | und        |           | yes

Для этого правила используется алгоритм сортировки по умолчанию технического стандарта UNICODE.

ICU: канонизация локалей
commit: ea1db8ae, 1671f990

Названия локалей для провайдера ICU будут приводиться к каноническому виду в соответствии с правилами BCP 47. Заодно это предотвратит появление ошибочных правил сортировки: 

CREATE COLLATION test_icu(provider=icu, locale='invalid_locale');
NOTICE:  using standard form "invalid-locale" for locale "invalid_locale"
ERROR:  ICU locale "invalid-locale" has unknown language "invalid"
HINT:  To disable ICU locale validation, set parameter icu_validation_level to DISABLED.

Для отключения проверки локалей можно использовать новый параметр icu_validation_level (второй коммит).

ICU: пользовательские правила для адаптации алгоритма сортировки
commit: 30a53b79

Новый параметр rules команды CREATE COLLATION позволяет настроить алгоритм сортировки под конкретные нужды.

Создадим правило сортировки, где времена года сортируются начиная с зимы: 

CREATE COLLATION seasons (
    provider = icu, locale = 'ru', rules = '& З < В < Л < О'
);

WITH seasons(name) AS (
    VALUES ('Лето'), ('Зима'), ('Весна'), ('Осень')
)
SELECT * FROM seasons ORDER BY name COLLATE seasons;
 name  
-------
 Зима
 Весна
 Лето
 Осень
(4 rows)

Пользовательские правила можно также указывать в команде CREATE DATABASE и при вызове утилит createdb, initdb.

См. также:
ICU Documentation: Collation Customization
How collation works (Peter Eisentraut)
How collation of punctuation and whitespace works (Peter Eisentraut)


Безопасность

libpq: новый параметр require_auth
commit: 3a465cc6

Файл pg_hba.conf настроен следующим образом: 

SELECT rule_number rule, type, database, user_name, auth_method
FROM pg_hba_file_rules
WHERE database != '{replication}';
 rule | type  | database | user_name |  auth_method  
------+-------+----------+-----------+---------------
    1 | local | {all}    | {all}     | trust
    2 | host  | {all}    | {all}     | scram-sha-256

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

$ psql 'host=localhost require_auth=md5,scram-sha-256' -c 'SELECT system_user'
Password for user postgres:
      system_user       
------------------------
 scram-sha-256:postgres
(1 row)

В этом примере для подключения по TCP/IP должно использоваться второе правило с методом scram-sha-256. Поскольку этот метод, вместе с md5, перечислен клиентом в параметре require_auth, то он и применяется. (О новой функции 16-й версии system_user можно прочитать в статье о сентябрьском коммитфесте.)

Если же метод аутентификации в require_auth не соответствует методу из pg_hba.conf, то подключиться не получится: 

$ psql 'host=localhost require_auth=md5' -c 'SELECT system_user'
psql: error: connection to server at "localhost" (127.0.0.1), port 5416 failed: auth method "md5" requirement failed: server requested SASL authentication

Можно указать методы, которые нельзя использовать. Для этого перед методом ставится восклицательный знак: 

psql 'host=localhost require_auth=!md5,!password' -c 'SELECT system_user'
Password for user postgres:
      system_user       
------------------------
 scram-sha-256:postgres
(1 row)

Специальное значение none используется, когда требуется подключение без аутентификации. В нашем случае это сработает для локального подключения из первого правила в pg_hba.conf: 

$ psql 'require_auth=none' -c 'SELECT system_user'
 system_user
-------------

(1 row)

Вместо параметра require_auth можно использовать переменную окружения PGREQUIREAUTH.

См. также
Postgres 16 highlight — require_auth for libpq (Michael Paquier)

scram_iterations: счетчик итераций для шифрования паролей с использованием SCRAM-SHA-256
commit: b5777430

В предыдущих версиях значение счетчика зашито в коде и равняется 4096.

Теперь можно генерировать более устойчивые к атакам пароли, увеличив значение счетчика итераций в новом параметре scram_iterations. В RFC 7677 рекомендуется не менее 15000.

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

См. также
Postgres 16 highlight — Control of SCRAM iterations (Michael Paquier)


Функции и команды SQL

Поддержка стандарта SQL/JSON
commit: 7081ac46, 6ee30209

Эта большая работа была принята еще год назад, но все-таки незадолго до выхода 15-й версии ее откатили.

Со второй попытки в 16-й версию попали функции-конструкторы (JSON_ARRAY, JSON_ARRAYAGG, JSON_OBJECT, JSON_OBJECTAGG) и предикаты (IS JSON [VALUE], IS JSON ARRAY, IS JSON OBJECT, IS JSON SCALAR) .

Работа над остальными частями будет продолжена в 17-й версии.

Новые функции pg_input_error_info и pg_input_is_valid
commit: b8da37b3, 1939d262

Функция pg_input_is_valid проверяет, является ли первый аргумент допустимым входным значением для типа данных из второго аргумента: 

SELECT pg_input_is_valid('value', 'jsonb');
 pg_input_is_valid
-------------------
 f

Функция pg_input_error_info выдаст подробную информацию об ошибке: 

SELECT * FROM pg_input_error_info('value', 'jsonb')\gx
-[ RECORD 1 ]--+-----------------------------------
message        | invalid input syntax for type json
detail         | Token "value" is invalid.
hint           |
sql_error_code | 22P02

Функции реализуют «мягкую» (soft) проверку входных значений, отсутствие которой в прошлом релизном цикле не позволило включить SQL/JSON в 15-ю версию.

См. также
Waiting for PostgreSQL 16 — Add test scaffolding for soft error reporting from input functions (Hubert 'depesz' Lubaczewski)

Фонетический алгоритм Daitch-Mokotoff Soundex
commit: a290378a

Поддержка фонетического алгоритма Daitch-Mokotoff Soundex добавлена в расширение fuzzystrmatch.

CREATE EXTENSION fuzzystrmatch;

SELECT daitch_mokotoff('Holubica'),
       daitch_mokotoff('Golubitsa'),
       daitch_mokotoff('Holubica') && daitch_mokotoff('Golubitsa');
 daitch_mokotoff | daitch_mokotoff | ?column?
-----------------+-----------------+----------
 {587500,587400} | {587400}        | t

См. также
Soundexing and Genealogy by Gary Mokotoff

Новые функции array_shuffle и array_sample
commit: 888f2ea0

Функция array_shuffle перемешивает элементы исходного массива, а array_sample возвращает массив с указанным количеством случайных элементов исходного массива: 

WITH a(digits) AS (
    SELECT '{1,2,3,4,5,6,7,8,9,0}'::int[]
)
SELECT array_shuffle(a.digits),
       array_sample(a.digits, 3)
FROM a;
     array_shuffle     | array_sample
-----------------------+--------------
 {1,0,9,8,2,3,5,7,4,6} | {9,1,4}

Функции могут пригодиться для работы с методом Монте-Карло.

См. также
Waiting for PostgreSQL 16 — Add array_sample () and array_shuffle () functions (Hubert 'depesz' Lubaczewski)

Новая агрегатная функция any_value
commit: 2ddab010

Описанная в стандарте SQL агрегатная функция any_value возвращает произвольное непустое значение для группы строк.

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

CREATE TABLE t (id int);
INSERT INTO t VALUES(1),(1),(2),(3),(3) RETURNING *;
 id
----
  1
  1
  2
  3
  3
(5 rows)

Требуется удалить дубли одним запросом. (Подобного рода задачи встречаются на собеседованиях.)

Для выделения одной из двух дублированных записей обычно применяют функции min или max к системному столбцу ctid. Это работает, но немного вводит в заблуждение, ведь нам не нужно минимальное или максимальное значение. Нам достаточно любое из двух и новая функция any_value отлично вписывается в решение: 

DELETE FROM t 
WHERE ctid IN (SELECT any_value(ctid)
               FROM t 
               GROUP BY id HAVING count(*) = 2
);
DELETE 2
SELECT * FROM t;
 id
----
  1
  2
  3
(3 rows)

COPY: загрузка значений по умолчанию
commit: 9f8377f7

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

CREATE TABLE periods (
    id int PRIMARY KEY,
    date_from date NOT NULL DEFAULT '0001-01-01',
    date_to   date NOT NULL DEFAULT '3999-12-31'
);

Данные в таблицу загружаются командой COPY из файлов. Поставщики файлов вместо отсутствующих дат проставляют N/A. Повлиять на формирование файлов возможности нет. 

\! cat data.txt
1    2023-04-01    2023-05-01
2    2023-04-08    N/A
3    N/A    2023-04-22

Загрузить такой файл без предварительных преобразований поможет новый параметр DEFAULT команды COPY. Когда указанное в нем значение встречается в данных, вычисляется значение по умолчанию для соответствующего столбца: 

\COPY periods FROM 'data.txt' (DEFAULT 'N/A');

SELECT * FROM periods;
 id | date_from  |  date_to   
----+------------+------------
  1 | 2023-04-01 | 2023-05-01
  2 | 2023-04-08 | 3999-12-31
  3 | 0001-01-01 | 2023-04-22
(3 rows)

timestamptz: добавление и вычитание интервалов времени
commit: 75bd846b

Операторы + и — можно использовать для добавления и вычитания интервалов из значений типа время с часовым поясом (timestamptz). Результат таких операций зависит от установленного в параметре timezone часового пояса.

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

SET timezone = 'UTC';

SELECT '2021-10-31 00:00:00+02'::timestamptz + '1 day'::interval,
     date_add('2021-10-31 00:00:00+02'::timestamptz, '1 day'::interval, 'Europe/Warsaw');
        ?column?        |        date_add        
------------------------+------------------------
 2021-10-31 22:00:00+00 | 2021-10-31 23:00:00+00

Параметр для часового пояса также появился в функции generate_series.

Независимость от параметров сеанса позволила объявить как immutable все три функции в варианте с часовым поясом.

XML: форматирование значений
commit: 483bdb2a

У функции XMLSERIALIZE появился параметр INDENT для форматирования вывода: 

SELECT XMLSERIALIZE(
    DOCUMENT 'Support [NO] INDENT option in XMLSERIALIZE()Jim JonesPeter Smith and Tom LaneTom Lane' AS TEXT
    INDENT);
                        xmlserialize                         
-------------------------------------------------------------
                                                   +
   Support [NO] INDENT option in XMLSERIALIZE()+
   Jim Jones                               +
   Peter Smith and Tom Lane          +
   Tom Lane                          +
                                                  +

pg_size_bytes: поддержка «B»
commit: ce1215d9

Для некоторых параметров значение можно задавать в байтах.

SHOW log_parameter_max_length;
 log_parameter_max_length
--------------------------
 512B

Требуется запросом получить число байт. Используем pg_size_bytes: 

SELECT pg_size_bytes(current_setting('log_parameter_max_length'));
 pg_size_bytes
---------------
           512

В предыдущих версиях функция pg_size_bytes выдаст ошибку, потому что понимает только bytes, но не B.

Новые функции: erf, erfc
commit: d5d57414

В дополнение к random_normal в 16-й версии появилась функция ошибок erf и дополнительная функцией ошибок erfc. На правило 68–95–99.7 теперь можно посмотреть так: 

SELECT erf(1 / sqrt(2)) AS erf_1,
       erf(2 / sqrt(2)) AS erf_2,
       erf(3 / sqrt(2)) AS erf_3;
       erf_1        |       erf_2        |       erf_3        
--------------------+--------------------+--------------------
 0.6826894921370859 | 0.9544997361036416 | 0.9973002039367398

Функция erfc возвращает значение близкое к 1 — erf ().


Производительность

Параллельное выполнение полного и правого хеш-соединения
commit: 11c2d6fd

В предыдущих версиях следующий запрос всегда выполнялся последовательно. В 16-й версии доступно параллельное выполнение: 

EXPLAIN (costs off)
SELECT count(*)
FROM bookings b FULL OUTER JOIN tickets t USING (book_ref);
                          QUERY PLAN                           
---------------------------------------------------------------
 Finalize Aggregate
   ->  Gather
         Workers Planned: 2
         ->  Partial Aggregate
               ->  Parallel Hash Full Join
                     Hash Cond: (t.book_ref = b.book_ref)
                     ->  Parallel Seq Scan on tickets t
                     ->  Parallel Hash
                           ->  Parallel Seq Scan on bookings b

Варианты реализации правого антисоединения
commit: 16dc2703

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

15=# EXPLAIN
SELECT * FROM flights f
WHERE NOT EXISTS (SELECT 1 FROM ticket_flights t WHERE t.flight_id = f.flight_id);
                                      QUERY PLAN                                       
---------------------------------------------------------------------------------------
 Hash Anti Join  (cost=291530.67..338842.08 rows=133627 width=63)
   Hash Cond: (f.flight_id = t.flight_id)
   ->  Seq Scan on flights f  (cost=0.00..4772.67 rows=214867 width=63)
   ->  Hash  (cost=153851.52..153851.52 rows=8391852 width=4)
         ->  Seq Scan on ticket_flights t  (cost=0.00..153851.52 rows=8391852 width=4)

Для выполнения запроса в 15-й версии используется антисоединение (узел Hash Anti Join). Хеш-таблица строится по ticket_flights ― большей из двух таблиц запроса. На создание хеш-таблицы потребуются соответству

© Habrahabr.ru