PostgreSQL 14: Часть 3 или «ноябрьское затишье» (Коммитфест 2020-11)

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

Громкими киллер-фичами нас не побаловали, надеемся разработчики припрятали их на последние два коммитфеста в январе и марте следующего года. Тем не менее, рассказать есть о чем. Например разберемся с такими вопросами:

• Не пора ли увеличивать wal_buffers?
• Можно ли перегружать хранимые подпрограммы по OUT-параметрам?
• По умолчанию pg_stat_statements собирает данные о 5000 запросов. Как понять много это или мало?
• Что будет, если в операционной системе обновится библиотека libc?

Документация

Документирование установки пакетных сборок
commit: 5b36221c

Сделан еще один шаг к документированию пакетных сборок. В предыдущей статье серии уже говорились про изменения в главе об управлении сервером. Новая глава документации Installation from Binaries не может похвастаться подробным описанием. В ней всего четыре предложения. Но она официально отсылает к разделу Download, где находятся подробные описания установки для поддерживаемых платформ.

Мониторинг

Добавлена информация о странице, где произошла ошибка в процессе восстановления
commit: 9d0bd95f

В текст сообщения об ошибке при восстановлении добавлена информация о том, в каком файле и на какой странице (блоке) эта ошибка произошла. Будет полезным, например, при диагностике проблем с применением WAL-записей на физической реплике, без использования pg_waldump.

Новое представление pg_stat_wal
commit: 8d9a9359, 01469241

Всем любителям статистики! Новое представление для мониторинга и настройки WAL-буфера ― pg_stat_wal. Например, столбец wal_buffers_full показывает сколько раз данные сбрасывались на диск из-за переполнения буфера. На основе этой информации можно принимать решение об изменении wal_buffers.


Новое представление pg_stat_statements_info
commit: 9fbc3f31

Количество отслеживаемых команд в pg_stat_statements задается параметром max и по умолчанию равно 5000. Много это или мало? А если мало, то насколько? Как правильно определить размер?

Для ответов на эти вопросы нас вооружили представлением pg_stat_statements_info. Пока в нем всего один столбец dealloc и он показывает сколько раз значение max было превышено и наименее ресурсоемкие запросы отбрасывались. Большое значение этого счетчика — сигнал для увеличения pg_stat_statements.max.

pg_stat_statements: отслеживание количества обработанных строк для REFRESH MATERIALIZED VIEW
commit: b62e6056

В статье об июльском коммитфесте уже писалось о том, что в pg_stat_statements добавили поддержку счетчика обработанных записей для CREATE TABLE AS, SELECT INTO, CREATE MATERIALIZED VIEW, FETCH. Теперь к этим командам добавилась REFRESH MATERIALIZED VIEW.

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

Ускорение проверки на соответствие строки определенной форме нормализации Unicode и самой нормализации строк Unicode
commit: 2a731645, 80f8eb79, 783f0cc6

Ускорение достигнуто за счет более оптимальной генерации хеш-функций (первый патч). В результате (второй коммит) выражение IS [form] NORMALIZED стало работать быстрее, а сама нормализация (функция normalize) ускорена в несколько раз (третий коммит).

Подсказка компилятору о низкой вероятности обработки ошибок
commit: 913ec71d

Суть оптимизации в подсказке компилятору о том, что ветки кода с вызовами elog(ERROR) или ereport(ERROR) выполняются с меньшей вероятностью, чем остальные.

Параллельное выполнение CREATE INDEX CONCURRENTLY
commit: c98763bf

Команда CREATE INDEX CONCURRENTLY, для неблокирующего создания индекса, сама блокируется другими командами CREATE INDEX CONCURRENTLY. В результате создание таким способом большого индекса заблокирует создание индексов меньшего размера.

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

На очереди сделать аналогичный патч для REINDEX CONCURRENTLY.

Кеширование результата конвертации now() во внутренние структуры памяти
commit: 0a87ddff

Если в одной транзакции выполняется много вызовов таких функций как CURRENT_DATE, CURRENT_TIME, LOCALTIME, то они будут использовать кешированное значение. От этого выиграют транзакции, загружающие большое количество строк командой COPY в таблицу, где столбцы имеют такие функции в качестве значений по умолчанию.

Репликация

Запись в журнал сервера о проблемах выполнения restore_command
commit: a4ef0329

Команда restore_command на реплике может выполниться успешно, но по каким-то причинам не восстановить WAL-файл из архива. Последующее восстановление завершится не очень информативной ошибкой.

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

pg_rewind: использование реплики в качестве исходного сервера
commit: 9c4f5192

В качестве исходного сервера pg_rewind теперь сможет использовать реплику. Раньше это было невозможно из-за того, что утилита создавала временную таблицу на исходном сервере. Теперь код переписан, временные таблицы больше не нужны.

Логическая репликация: детализация сообщения об ошибке применения записей на подписчике
commit: f0770709

Если в таблице на подписчике не хватает столбцов, то в сообщении об ошибке отсутствующие столбцы будут перечислены.

Сервер

UPD: Следующий патч откатили: ec483147
Отслеживание изменения версий правил сортировки у индексов
commit: 7d1297df, cd6f479e, 257836a7

У правил сортировки ICU был механизм отслеживания версий. При создании правила сортировки ICU номер версии сохранялся в pg_collation.collversion. Выполняя запрос к любому объекту, использующему это правило сортировки, сначала проверялось соответствие версий в pg_collation и в операционное системе. Если отличаются ― выдавалось предупреждение о том, что нужно пересоздать все объекты с этим правилом сортировки. А затем можно было выполнить ALTER COLLATION… REFRESH VERSION для обновления версии в системном каталоге.

Больше такого механизма нет. А в pg_collation больше нет столбца collversion (первый коммит). И на то были причины. В таком виде механизм нельзя использовать для правила сортировки по умолчанию. А это один из барьеров для использования правил сортировки ICU при инициализации кластера или создании базы данных. Кроме того, какие-то объекты (индексы) могут быть созданы уже после расхождения версий. И нет возможности отследить какие именно объекты были созданы со старой версией (требуют перестройки), а какие с новой (можно оставить).

Новое место для хранения версии ― pg_depend.refobjversion (второй коммит). Именно здесь будет храниться версия правила сортировки (третий коммит).

Вот как работает новый механизм. Создаем таблицу и индекс:

CREATE TABLE t (col text COLLATE "ru-x-icu");
CREATE INDEX t_ind ON t(col);

Теперь индекс зависит от правила сортировки ru-x-icu и конкретно от версии 153.80.32.1:

SELECT (SELECT pc.collname FROM pg_collation pc
        WHERE pc.oid = pd.refobjid
       ) AS refobj
       ,refobjversion
FROM pg_depend pd
WHERE classid = 'pg_class'::regclass
AND objid = 't_ind'::regclass
AND refclassid = 'pg_collation'::regclass;

  refobj  | refobjversion
----------+---------------
 ru-x-icu | 153.80.32.1

Если в библиотеке ICU изменится версия этого правила, то при обращении к индексу t_ind будет выдаваться предупреждение о необходимости его перестройки. Что и нужно сделать командой REINDEX, которая заодно обновит версию в pg_depend. Просто обновить версию можно и командой ALTER INDEX… ALTER COLLATION… REFRESH VERSION, но при уверенности, что индекс перестраивать не нужно.

Хоть и с оговорками, но механизм может работать не только для правил сортировки ICU, но и для libc!

А что если для индекса используется правило сортировки по умолчанию. Будет ли отслеживаться его версия?

ALTER TABLE t ADD col_default text;
CREATE INDEX t_ind_default ON t(col_default);

SELECT (SELECT pc.collname FROM pg_collation pc
        WHERE pc.oid = pd.refobjid
       ) AS refobj
       ,refobjversion
FROM pg_depend pd
WHERE classid = 'pg_class'::regclass
AND objid = 't_ind_default'::regclass
AND refclassid = 'pg_collation'::regclass;

 refobj  | refobjversion
---------+---------------
 default | 2.27

Да, будет. С теми же оговорками для libc.


postgres_fdw: автоматическое повторное подключение, если сеанс на внешнем сервере больше недоступен
commit: 32a9c0bd

Используемый в postgres_fdw сеанс на внешнем сервере может оказаться недоступным. Например он мог аварийно завершиться или внешний сервер был перезагружен. Попытка выполнить запрос к внешней таблице в таком случае завершается ошибкой.

Теперь будет предприниматься попытка создать новое подключение и всё-таки выполнить запрос.

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

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

CREATE USER MAPPING FOR postgres SERVER demo_srv
OPTIONS (user 'postgres', password 'postgres');

IMPORT FOREIGN SCHEMA bookings
    LIMIT TO (airports)
    FROM SERVER demo_srv
    INTO public;

Внешняя таблица создана, к ней можно обращаться:

SELECT count(*) FROM airports;

 count
-------
   104

Найдем обслуживающий внешний процесс и прервем его:

SELECT datname, pid  FROM pg_stat_activity WHERE application_name = 'postgres_fdw';

 datname | pid  
---------+------
 demo    | 8943
(1 row)

SELECT pg_terminate_backend(8943);

 pg_terminate_backend
----------------------
 t

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

SELECT count(*) FROM airports;

 count
-------
   104

Теперь нас обслуживает другой процесс:

SELECT datname, pid  FROM pg_stat_activity WHERE application_name = 'postgres_fdw';

 datname | pid  
---------+------
 demo    | 8966

Операторы |>> и <<| для типа point
commit: 0cc99327

В документации к геометрическим функциям и операторам по 13 версии находим описание для оператора |>>

Первый объект строго выше второго? Имеется для типов box, polygon, circle

И аналогичное для <<| (строго ниже).

А вот для точек (тип point) эти операторы не поддерживаются, хотя есть похожие: >^ и <^.

В результате доработки появилась поддержка |>> и <<| для точек:

SELECT '(0,1)'::point |>> '(0,0)'::point;

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

Операторы >^ и <^ остались, но пользоваться ими больше не рекомендуется, в будущем они будут удалены.

pg_hba.conf: пересмотр параметра clientcert
commit: 253f1025

Общий параметр аутентификации clientcert теперь поддерживает строковые значения verify-ca и verify-full вместо предыдущих 1 и 0.

Также больше не поддерживается значение no-verify, т.к. это то же самое как и просто не задавать значение параметру clientcert.

Хеш-функции для составных типов
commit: 01e658fa

Новые хеш-функции для типа record:

\df hash_record*

                                 List of functions
   Schema   |         Name         | Result data type | Argument data types | Type
------------+----------------------+------------------+---------------------+------
 pg_catalog | hash_record          | integer          | record              | func
 pg_catalog | hash_record_extended | bigint           | record, bigint      | func

Планы запросов, использующие хеширование, смогут применяться для операций с составными типами UNION/INTERSECT/EXCEPT DISTINCT, рекурсивных запросов с UNION DISTINCT, соединений хешированием и операций с секционированными по хешу таблицами.

pg_trgm: поддержка оператора =
commit: 935f6666

Индекс созданный для класса оператора gin_trgm_ops или gist_trgm_ops используется для запросов с условием

column LIKE 'строка'

Но не используется для равнозначного условия

column = 'строка' 

Поддержку оператора равенства добавили в pg_trgm.

Команды SQL

CREATE [OR REPLACE] TRIGGER
commit: 92bf7e2d

Командой CREATE TRIGGER теперь можно не только создать новый, но и изменить определение существующего триггера, добавив OR REPLACE. Заменить можно всё, кроме имени триггера и таблицы, для которой триггер срабатывает.

OUT-параметры в процедурах
commit: 2453ea14

Параметры INOUT поддерживаются с момента появления процедур в версии 11, однако реализация OUT-параметров была отложена. Теперь закрыт и этот пробел.

CREATE PROCEDURE multiply (a int, b int, OUT x int) AS $$
BEGIN
    x := a * b;
END;
$$ LANGUAGE plpgsql;

В отличие от функций, OUT-параметры являются частью сигнатуры процедуры. Поэтому их нужно обязательно указывать при вызове. Если такая процедура вызывается напрямую оператором CALL, то в качестве значения можно указать NULL:

CALL multiply(2,2,NULL);

 x
---
 4

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

DO $$
DECLARE
    v int;
BEGIN
    CALL multiply(5,5,v);
    RAISE NOTICE '%', v;
END;$$;

NOTICE:  25
DO

Поиск элемента от конца строки в функции split_part
commit: ec0294fb

Функция split_part теперь понимает, что если номер возвращаемого элемента меньше нуля, то искать нужно с конца строки:

SELECT split_part('1:2:3', ':', -1);

 split_part
------------
 3

Системное администрирование

pg_upgrade --check: добавлена проверка на существование каталогов для табличных пространств
commit: 3c0471b5

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

Теперь pg_upgrade --check проверяет наличие каталогов табличных пространств, что позволяет обнаружить проблему до запуска обновления.

Изменение портировали в предыдущие версии, по 9.5 включительно.

pg_upgrade: скрипт analyze_new_cluster больше не создается
commit: 8f113698

После завершения обновления создавался скрипт analyze_new_cluster, содержащий всего одну команду для скорейшего сбора статистики в несколько проходов:

vacuumdb --all --analyze-in-stages

Больше этого скрипта не будет, а вместо рекомендации запустить analyze_new_cluster будет рекомендация выполнить vacuumdb.

Поддержка абстрактных Unix-сокетов
commit: c9f0624b

Абстрактные сокеты вместо файловой системы используют «абстрактное» пространство имен. Имена таких сокетов начинаются с @.

Поддержка добавлена для Linux и Windows.

Разное

Тип данных для значений по умолчанию в функциях LEAD/LAG
commit: 5c292e6b, 9e38c2bb

Оконные функции LEAD и LAG могут принимать третий аргумент ― значение по умолчанию. И это значение должно быть такого же типа как и первый аргумент с данными (anyelement):

\df lag

                                List of functions
   Schema   | Name | Result data type |       Argument data types       |  Type  
------------+------+------------------+---------------------------------+--------
 pg_catalog | lag  | anyelement       | anyelement                      | window
 pg_catalog | lag  | anyelement       | anyelement, integer             | window
 pg_catalog | lag  | anyelement       | anyelement, integer, anyelement | window

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

SELECT amount, LAG(amount, 1, 0) OVER (ORDER BY amount)
FROM ticket_flights
WHERE ticket_no = '0005432659770'
ORDER BY amount;

ERROR:  function lag(numeric, integer, integer) does not exist
...

Если сделать явное приведение 0 к типу numeric, а столбец amount имеет тип numeric, то запрос будет работать. Однако стандарт SQL предполагает, что значение по умолчанию может быть другого типа, но приводимого к типу первого аргумента. Столкнувшись с таким поведением, Маркус Винанд в таблицу совместимости оконных функций стандарту SQL добавил сноску для PostgreSQL о том, что в этой СУБД значения по умолчанию не поддерживаются вообще.

В 14 версии у функций LEAD и LAG заменили тип данных первого и третьего аргумента на появившийся в 13 версии anycompatible. Явное преобразование типа больше не требуется:

SELECT amount, LAG(amount, 1, 0) OVER (ORDER BY amount)
FROM ticket_flights
WHERE ticket_no = '0005432659770'
ORDER BY amount;

  amount   |   lag    
-----------+----------
   5900.00 |        0
  18000.00 |  5900.00
 199300.00 | 18000.00
(3 rows)

Более того, подобную замену типа с anyelement на anycompatible сделали для многих функций, работающих с массивами (второй коммит). Например теперь можно добавлять в массив совместимые по типу элементы:

SELECT array_append(ARRAY[1.0, 2.0], 0);

 array_append
--------------
 {1.0,2.0,0}

Новый модуль contrib: old_snapshot
commit: aecf5ee2

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

У DBA есть два инструмента «борьбы» с долгими транзакциями. Кроме параметра idle_in_transaction_session_timeout есть еще old_snapshot_threshold.

В old_snapshot_threshold задается интервал времени, в течение которого нужные снимку версии строк не будут очищаться. После этого очистка имеет право удалить эти версии строк, а транзакция при обращении к ним получит ошибку «snapshot too old».

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

Установим old_snapshot_threshold в один час:

ALTER SYSTEM SET old_snapshot_threshold = '1h';

Перезагружаем сервер для применения изменений и создаем расширение:

CREATE EXTENSION old_snapshot;

Запускаем тест pgbench на 5 минут с интенсивностью 10 транзакций в секунду:

pgbench -T 300 -R 10

В расширении одна функция ― pg_old_snapshot_time_mapping, данные которой обновляются раз в минуту. Через несколько минут смотрим:

SELECT * FROM pg_old_snapshot_time_mapping();

 array_offset |     end_timestamp      | newest_xmin
--------------+------------------------+-------------
            0 | 2020-12-14 09:01:00+03 |        3341
            1 | 2020-12-14 09:02:00+03 |        3343
            2 | 2020-12-14 09:03:00+03 |        3874
            3 | 2020-12-14 09:04:00+03 |        4494
            4 | 2020-12-14 09:05:00+03 |        5101
            5 | 2020-12-14 09:06:00+03 |        5706
            6 | 2020-12-14 09:07:00+03 |        6293

Как видим, с каждой минутой(end_timestamp) значение newest_xmin продвигается примерно на 600 транзакций.

---

На этом пока всё. Продолжение следует после январского коммитфеста.