Ноябрьский коммитфест принес немало интересного! Без лишних предисловий приступаем к обзору.
Самое интересное об июльском и сентябрьском коммитфестах ― в предыдущих статьях серии: 2023-07, 2023-09.
Триггер ON LOGIN
Триггеры событий для REINDEX
ALTER OPERATOR: commutator, negator, hashes, merges
pg_dump —filter=dump.txt
psql: отображение привилегий по умолчанию
pg_stat_statements: отслеживание времени появления оператора и сброс min/max статистики
pg_stat_checkpointer: статистика процесса контрольной точки
pg_stats: статистика столбцов диапазонных типов
Планировщик: исключение лишних соединений таблицы самой с собой
Планировщик: статистика материализованных CTE
Планировщик: доступ к таблице по нескольким условиям
Оптимизация просмотра индекса при поиске по диапазону
dblink, postgres_fdw: детализированные события ожидания
Логическая репликация: перенос слотов репликации при обновлении сервера публикации
Журналирование использования слотов репликации
Unicode: новые информационные функции
Новая функция xmltext
Поддержка AT LOCAL
Бесконечные интервалы
ALTER SYSTEM с неизвестными пользовательскими параметрами
Сборка сервера из исходных кодов
Триггер ON LOGIN
commit: e83d1b0c
В будущей версии появится возможность создавать триггер события на подключение к базе данных.
Как обычно, триггер создается в два этапа. Сначала триггерная функция:
CREATE FUNCTION check_login() RETURNS event_trigger AS $$ BEGIN IF session_user = 'postgres' THEN RETURN; END IF; IF to_char(current_date, 'DY') IN ('SAT','SUN') THEN RAISE 'Хороших выходных, увидимся в понедельник!'; END IF; END; $$ LANGUAGE plpgsql;
Затем сам триггер:
CREATE EVENT TRIGGER check_login ON LOGIN EXECUTE FUNCTION check_login();
Теперь можно быть уверенными, что по выходным, к всеобщему удовольствию, пользователи не будут мешать администратору. ?
$ psql -U alice -d postgres
psql: error: connection to server on socket "/tmp/.s.PGSQL.5401" failed: FATAL: Хороших выходных, увидимся в понедельник! CONTEXT: PL/pgSQL function check_login() line 6 at RAISE
Триггеры событий для REINDEX
commit: f21848de
Триггеры событий теперь срабатывают для команды REINDEX. Их можно определить для событий ddl_command_start и ddl_command_end.
ALTER OPERATOR: commutator, negator, hashes, merges
commit: 2b5154be
В команде ALTER OPERATOR теперь можно указать коммутирующий оператор (COMMUTATOR) и обратный для него (NEGATOR), если эти операторы не были указаны в CREATE OPERATOR. А также добавить поддержку соединений хешированием (HASHES) и соединений слиянием (MERGES).
pg_dump —filter=dump.txt
commit: a5cf808b
В новом параметре --filter утилиты pg_dump можно указать имя файла, в котором перечислены объекты для включения или исключения из выгрузки:
$ cat dump.txt
include table bookings exclude table_data bookings
$ pg_dump -d demo --filter=dump.txt |grep -v -E '^SET|^SELECT|^--|^$'
CREATE TABLE bookings.bookings ( book_ref character(6) NOT NULL, book_date timestamp with time zone NOT NULL, total_amount numeric(10,2) NOT NULL ); ALTER TABLE bookings.bookings OWNER TO postgres; COMMENT ON TABLE bookings.bookings IS 'Bookings'; COMMENT ON COLUMN bookings.bookings.book_ref IS 'Booking number'; COMMENT ON COLUMN bookings.bookings.book_date IS 'Booking date'; COMMENT ON COLUMN bookings.bookings.total_amount IS 'Total booking cost'; ALTER TABLE ONLY bookings.bookings ADD CONSTRAINT bookings_pkey PRIMARY KEY (book_ref);
Подробности синтаксиса файла описаны в документации к утилите.
Параметр полезен, если перечень объектов базы данных настолько большой, что перечисление их в командной строке запуска pg_dump превышает допустимый размер команды ОС.
Параметр --filter также добавлен в pg_dumpall и pg_restore.
psql: отображение привилегий по умолчанию
commit: d1379ebf
Команды psql были неудобны для просмотра привилегий по умолчанию. Рассмотрим на примере схем, хотя сказанное далее относится к просмотру привилегий любых объектов.
CREATE SCHEMA s;
После создания схемы ее владелец по умолчанию имеет обе привилегии: USAGE и CREATE. Но команда \dn+ их не покажет, поскольку они не записаны в pg_namespace.nspacl, там сейчас NULL:
16=# SELECT nspacl IS NULL FROM pg_namespace WHERE oid = 's'::regnamespace;
?column? ---------- t
16=# \pset null '(null)' 16=# \dn+ s
List of schemas Name | Owner | Access privileges | Description ------+----------+-------------------+------------- s | postgres | |
Обратите внимание, что даже установка \pset null не повлияла на отображение NULL значений. Команды psql для просмотра объектов системного каталога игнорировали эту установку.
Выполнение любой команды GRANT или REVOKE приведет к тому, что привилегии по умолчанию будут явно записаны в системный каталог. Следующие две команды выдают и забирают привилегии на схему. По сути ничего не меняется, но привилегии по умолчанию появились в таблице и теперь видны:
16=# GRANT USAGE ON SCHEMA s TO public; 16=# REVOKE USAGE ON SCHEMA s FROM public; 16=# \dn+ s
List of schemas Name | Owner | Access privileges | Description ------+----------+----------------------+------------- s | postgres | postgres=UC/postgres |
Владелец может сам у себя отозвать привилегии (а потом выдать обратно):
16=# REVOKE ALL ON SCHEMA s FROM postgres;
Сейчас значение pg_namespace.nspacl представляет собой пустой массив типа aclitem[]. Пустой массив — это не NULL, но как это понять из вывода команды?
16=# \dn+ s
List of schemas Name | Owner | Access privileges | Description ------+----------+-------------------+------------- s | postgres | |
Только запросом к pg_namespace.
Что изменилось в 17-й версии. Установка \pset null теперь влияет на значения NULL:
17=# CREATE SCHEMA s; 17=# \pset null '(null)' 17=# \dn+ s
List of schemas Name | Owner | Access privileges | Description ------+----------+-------------------+------------- s | postgres | (null) | (null)
А отсутствие привилегий (пустой массив) отображается специальным значением (none):
17=# REVOKE ALL ON SCHEMA s FROM postgres; 17=# \dn+ s
List of schemas Name | Owner | Access privileges | Description ------+----------+-------------------+------------- s | postgres | (none) | (null)pg_stat_statements: отслеживание времени появления оператора и сброс min/max-статистики
commit: dc9f8a79
В pg_stat_statements новый столбец statssince, в котором фиксируется время начала сбора статистики каждого оператора. Также появилась возможность сбросить статистику столбцов min/max* для отдельных операторов вызовом pg_stat_stetments_reset с параметром minmax_only. Время сброса этой статистики фиксируется в столбце minmax_stats_since.
Изменения полезны для систем мониторинга, основанных на сэмплировании информации из pg_stat_statements. В частности, они позволят отказаться от сброса всей статистики перед созданием снимов.
pg_stat_checkpointer: статистика процесса контрольной точки
commit: 96f05261, 74604a37
Изменения буферного кеша на диск могут записывать процесс фоновой записи, процесс контрольной точки и процессы, обслуживающие пользователей. Соответствующая статистика долгое время отслеживалась в одном представлении pg_stat_bgwriter.
Теперь количество столбцов в pg_stat_bgwriter заметно уменьшилось. Информацию о статистике процесса контрольной точки перенесли в новое представление pg_stat_checkpointer (первый коммит).
В тоже время столбцы buffers_backend и buffers_backend_fsync удалили из pg_stat_bgwriter, так как более точную и детальную информацию об обслуживающих процессах можно получить из появившегося в 16-й версии представления pg_stat_io (второй коммит).
pg_stats: статистика столбцов диапазонных типов
commit: bc3c8db8
Статистика для столбцов диапазонных типов давно собирается и хранится в таблице pg_statistic. Однако эта информация не отображалась в представлении pg_stats, что данный патч и исправляет.
В pg_stats добавлены столбцы range_length_histogram, range_empty_frac, range_bounds_histogram.
Планировщик: исключение лишних соединений таблицы самой с собой
commit: d3d55ce5
В плохо написанном запросе таблица может без всякой необходимости соединяться сама с собой, синтаксис SQL этого не запрещает. Авторство таких запросов обычно за различными ORM, а оптимизацией часто приходится заниматься тем, кто не может повлиять на формирование запроса.
В 17-й версии планировщик научился вычислять подобные излишние соединения и не включать их в план запроса. В следующем примере используется ненужное полусоединение таблицы bookings самой с собой.
17=# EXPLAIN (costs off) WITH b AS ( SELECT book_ref FROM bookings ) SELECT * FROM bookings WHERE book_ref IN (SELECT book_ref FROM b);
QUERY PLAN ---------------------------------- Seq Scan on bookings Filter: (book_ref IS NOT NULL)
Планировщик справедливо решил, что достаточно один раз сканировать таблицу.
Этой оптимизацией управляет параметр enable_self_join_removal. Если его отключить, то увидим план запроса в предыдущих версиях:
17=# SET enable_self_join_removal = off; 17=# EXPLAIN (costs off) WITH b AS ( SELECT book_ref FROM bookings ) SELECT * FROM bookings WHERE book_ref IN (SELECT book_ref FROM b);
QUERY PLAN -------------------------------------------------------- Hash Join Hash Cond: (bookings.book_ref = bookings_1.book_ref) -> Seq Scan on bookings -> Hash -> Seq Scan on bookings bookings_1Планировщик: статистика материализованных CTE
commit: f7816aec
Добавим материализацию CTE в предыдущем примере и посмотрим на план выполнения запроса в 16-й версии (с отключенным jit):
16=# EXPLAIN (analyze,timing off) WITH b AS MATERIALIZED ( SELECT book_ref FROM bookings ) SELECT * FROM bookings WHERE book_ref IN (SELECT book_ref FROM b);
QUERY PLAN ------------------------------------------------------------------------------------------------------------------- Nested Loop (cost=82058.51..83469.66 rows=1055555 width=21) (actual rows=2111110 loops=1) CTE b -> Seq Scan on bookings bookings_1 (cost=0.00..34558.10 rows=2111110 width=7) (actual rows=2111110 loops=1) -> HashAggregate (cost=47499.98..47501.98 rows=200 width=28) (actual rows=2111110 loops=1) Group Key: b.book_ref Batches: 141 Memory Usage: 11113kB Disk Usage: 57936kB -> CTE Scan on b (cost=0.00..42222.20 rows=2111110 width=28) (actual rows=2111110 loops=1) -> Index Scan using bookings_pkey on bookings (cost=0.43..8.37 rows=1 width=21) (actual rows=1 loops=2111110) Index Cond: (book_ref = b.book_ref) Planning Time: 0.237 ms Execution Time: 10847.689 ms
Планировщик посчитал хорошей идеей сгруппировать CTE по book_ref, прежде чем соединять с внешним запросом (узел HashAggregate). Но book_ref — это первичный ключ таблицы bookings, поэтому количество строк после группировки не изменится, те же ~2 миллиона, а не 200 строк, как в оценке. Результат этой ошибки ― неправильно выбранной способ соединения (nested loop) CTE и внешнего запроса.
В 17-й версии планировщик может получить некоторую статистику о столбцах, входящих в материализованные CTE, и использовать её во внешних частях плана. Это позволяет точнее определять кардинальность соединений. Вот как теперь выполняется тот же запрос:
QUERY PLAN ------------------------------------------------------------------------------------------------------------------- Hash Semi Join (cost=117601.18..222997.93 rows=2111110 width=21) (actual rows=2111110 loops=1) Hash Cond: (bookings.book_ref = b.book_ref) CTE b -> Seq Scan on bookings bookings_1 (cost=0.00..34558.10 rows=2111110 width=7) (actual rows=2111110 loops=1) -> Seq Scan on bookings (cost=0.00..34558.10 rows=2111110 width=21) (actual rows=2111110 loops=1) -> Hash (cost=42222.20..42222.20 rows=2111110 width=28) (actual rows=2111110 loops=1) Buckets: 131072 Batches: 32 Memory Usage: 3529kB -> CTE Scan on b (cost=0.00..42222.20 rows=2111110 width=28) (actual rows=2111110 loops=1) Planning Time: 0.127 ms Execution Time: 1556.576 ms
Без лишней группировки, соединяя CTE и внешний запрос методом хеширования, запрос выполняется ~ в 7 раз быстрее.
Планировщик: доступ к таблице по нескольким условиям
commit: 5d8aa8bc
Нужно ли обращаться к таблице, для которой в запросе есть несколько условий? Небольшая оптимизация планировщика на эту тему.
Запрос в 16-й версии.
16=# EXPLAIN (costs off, analyze, timing off, summary off) SELECT * FROM tickets WHERE ticket_no = '0005432' AND ticket_no = '0005000';
QUERY PLAN ----------------------------------------------------------------- Result (actual rows=0 loops=1) One-Time Filter: false -> Index Scan using tickets_pkey on tickets (never executed) Index Cond: (ticket_no = '0005432'::bpchar)
Два условия для таблицы tickets противоречат друг другу, но планировщик не сразу об этом догадался и успел определить метод доступа к таблице. На более поздних этапах стало ясно, что к таблице не нужно обращаться, но усилия на планирование уже потрачены.
План этого же запроса в 17-й версии:
QUERY PLAN -------------------------------- Result (actual rows=0 loops=1) One-Time Filter: false
Оба условия проанализированы заранее. В результате затраты на построение плана сократились, да и выглядит аккуратнее.
Оптимизация просмотра индекса при поиске по диапазону
commit: e0b1ee17
Если для поиска по диапазону используется индекс типа B-дерево, то в каждой просмотренной странице индекса нужно найти значения, удовлетворяющие заданному диапазону.
В предыдущих версиях все значения индексных страниц проверялись на принадлежность диапазону. А начиная с 17-й версии выполняется предварительная проверка: если последнее значение на странице попадает в диапазон, то и все значения на этой странице тоже в него попадут и их можно не проверять. Чем больше страниц индекса нужно просмотреть и чем медленнее оператор сравнения, тем больший эффект от оптимизации.
В следующем примере просматривается более 40 тысяч индексных страниц. Запрос в 16-й версии:
16=# EXPLAIN (analyze, buffers, costs off, timing off) SELECT * FROM tickets WHERE ticket_no > '0005432' AND ticket_no < '0005434';
QUERY PLAN ------------------------------------------------------------------------------------- Index Scan using tickets_pkey on tickets (actual rows=1469571 loops=1) Index Cond: ((ticket_no > '0005432'::bpchar) AND (ticket_no < '0005434'::bpchar)) Buffers: shared hit=10675 read=30185 Planning: Buffers: shared read=4 Planning Time: 0.214 ms Execution Time: 683.801 ms
Этот же запрос в 17-й версии выполняется существенно быстрее:
QUERY PLAN ------------------------------------------------------------------------------------- Index Scan using tickets_pkey on tickets (actual rows=1469571 loops=1) Index Cond: ((ticket_no > '0005432'::bpchar) AND (ticket_no < '0005434'::bpchar)) Buffers: shared hit=10690 read=30170 Planning: Buffers: shared read=4 Planning Time: 0.268 ms Execution Time: 237.177 msdblink, postgres_fdw: детализированные события ожидания
commit: c789f0f6, d61f2538
Расширения dblink и postgres_fdw первыми воспользовались новым интерфейсом для создания событий ожидания. Описание добавленных событий ожидания можно найти в документации к каждому расширению.
Логическая репликация: перенос слотов репликации при обновлении сервера публикации
commit: 29d0a77f
Обновление сервера публикации на новую версию утилитой pg_upgrade ― одно из узких мест логической репликации. Публикации переносятся на новую версию, а вот слоты репликации нет. Это заставляет подписчиков заново синхронизировать данные после обновления сервера публикации.
Теперь pg_upgrade переносит на новый сервер слоты репликации, подписчикам остается только подстроить строку соединения и продолжить получать изменения.
Журналирование использования слотов репликации
commit: 7c3fb505
Включение параметра log_replication_commands, в дополнение к регистрации команд репликации, приведет к записи в журнал информации о получении/освобождении слотов репликации процессами wal sender.
Анализ сообщений в журнале поможет понять, как давно не используются слоты репликации и, соответственно, не работают должным образом их потребители (реплики, подписчики логической репликации и др.).
При включенном log_replication_commands сделаем резервную копию утилитой pg_basebackup. Теперь в журнале сервера можно найти новые сообщения:
LOG: acquired physical replication slot "pg_basebackup_339820" … LOG: released physical replication slot "pg_basebackup_339820"Unicode: новые информационные функции
commit: a02b37fc
Функция unicode_assigned возвращает истинное значение, если всем символам в строке присвоены кодовые позиции Unicode:
SELECT unicode_assigned('Привет, Мир!');
unicode_assigned ------------------ t
Еще две информационные функции возвращают версию Unicode для PostgreSQL и ICU соответственно:
SELECT unicode_version(), icu_unicode_version();
unicode_version | icu_unicode_version -----------------+--------------------- 15.1 | 14.0Новая функция xmltext
commit: 526fe0d7
Функция xmltext определена в стандарте SQL. Она конвертирует входную строку в значение XML, должным образом экранируя специальные символы:
SELECT xmltext('<Привет & Мир>');
xmltext -------------------------- <Привет & Мир>Поддержка AT LOCAL
commit: 97957fdb
Конструкция AT TIME ZONE позволяет явно указать часовой пояс для конвертации значений. В стандарте SQL имеется сокращение AT LOCAL для указания текущего часового пояса (установленного в параметре timezone). Теперь AT LOCAL можно использовать в PostgreSQL. Следующие два выражения эквивалентны для часового пояса ‘Europe/Moscow’:
SELECT now() AT TIME ZONE 'Europe/Moscow', now() AT LOCAL \gx
-[ RECORD 1 ]------------------------ timezone | 2023-12-18 12:57:29.612578 timezone | 2023-12-18 12:57:29.612578Бесконечные интервалы
commit: 519fc1bd
Тип interval понимает бесконечные значения:
SELECT 'infinity'::interval, '-infinity'::interval;
interval | interval ----------+----------- infinity | -infinity
Что позволяет выполнять некоторые арифметические операции:
SELECT now() + 'infinity'::interval, now() - 'infinity'::interval;
?column? | ?column? ----------+----------- infinity | -infinityALTER SYSTEM с неизвестными пользовательскими параметрами
commit: 2d870b4a
Команда ALTER SYSTEM научилась записывать в postgresql.auto.conf пользовательские параметры:
=# ALTER SYSTEM SET myapp.today = '2023-12-06'; ALTER SYSTEM
По умолчанию это может сделать только суперпользователь, но права на работу с отдельными параметрами можно передать командой GRANT .. ON PARAMETER.
Надо сказать, что ALTER SYSTEM и в предыдущих версиях понимает неизвестные пользовательские параметры, но только после того, как соответствующий параметр установлен в сеансе командой SET и записан в хеш-таблицу в памяти. Столь странное поведение сочли ошибкой и исправили.
Опять же, никто не запрещает записывать пользовательские параметры в основной конфигурационный файл postgresql.conf.
Сборка сервера из исходных кодов
commit: 721856ff
В tar-архивах с исходным кодом больше не будет файлов, предварительно сгенерированных утилитами Flex, Bison, perl. Также не будет сгенерированных файлов документации и страниц man. Поэтому сборка из исходных кодов, полученных из раздела Downloads, не будет отличаться от сборки из исходных кодов, полученных из git-репозитория. А это значит, что в любом случае для сборки потребуются flex, bison и perl.
Сделано в первую очередь для сборочной системы meson, в которой затруднительно собирать сервер из tar-архивов в нынешнем виде.
В этом году всё. Ждем следующего, январского коммитфеста 17-й версии.
ссылка на оригинал статьи https://habr.com/ru/articles/782042/
Добавить комментарий