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 транзакций.



    На этом пока всё. Продолжение следует после январского коммитфеста.
    Postgres Professional
    Разработчик СУБД Postgres Pro

    Похожие публикации

    Комментарии 2

      0
      Джва года жду возможность создавать временные таблицы на read-only реплике. Но, говорят, этот функционал реализовать очень нетривиально.
        0
        Два года уже Павел Стехуле работает над schema variables. Это не совсем временные таблицы, но что-то можно будет сделать… если примут.

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

      Самое читаемое