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
Если в таблице на подписчике не хватает столбцов, то в сообщении об ошибке отсутствующие столбцы будут перечислены.
Сервер
Отслеживание изменения версий правил сортировки у индексов
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 транзакций.
На этом пока всё. Продолжение следует после январского коммитфеста.
