Продолжаем следить за новинками 17-й версии PostgreSQL. На этот раз поговорим о том, чем запомнился сентябрьский коммитфест.
Самое интересное об июльском коммитфесте ― в предыдущей статье серии: 2023-07.
Удален параметр old_snapshot_threshold
Новый параметр event_triggers
Новые функции to_bin и to_oct
Новое системное представление pg_wait_events
EXPLAIN: счетчик времени на JIT-компиляцию преобразования кортежей
Планировщик: уточнение оценки начальной стоимости узла WindowAgg
pg_constraint: ограничения NOT NULL
Нормализация CALL, DEALLOCATE и команд управления двухфазной фиксацией
unaccent: целевое выражение правил поддерживает значения в кавычках
COPY FROM: FORCE_NOT_NULL и FORCE_NULL
Аудит подключений без аутентификации
pg_stat_subscription: новый столбец worker_type
Функция pg_promote при неуспешном переключении на реплику
Выбор метода синхронизации с диском в утилитах сервера
pg_restore: оптимизация восстановления в несколько потоков большого количества таблиц
pg_basebackup и pg_receivewal с параметром dbname
Имена параметров для ряда встроенных функций
psql: \watch min_rows
Удален параметр old_snapshot_threshold
commit: f691f5b8
В версии 9.6 появился параметр old_snapshot_threshold. Параметр определяет время для снимка данных, в течение которого гарантируется, что старые версии строк не будут удалены. По истечении этого времени очистка имеет право вычищать версии строк, видимые в снимке. При обращении к удаленным версиям строк транзакция получит ошибку, так знакомую пользователям Oracle: «Snapshot too old».
В реализации обнаружили проблемы, в том числе с производительностью, а элегантного решения пока не нашлось. Поэтому параметр удалили.
Новый параметр event_triggers
commit: 7750fefd
Параметр event_triggers включает/отключает триггеры событий.
\dconfig+ event_triggers
List of configuration parameters Parameter | Value | Type | Context | Access privileges ----------------+-------+------+-----------+------------------- event_triggers | on | bool | superuser | (1 row)
Предназначен в первую очередь для отладки триггеров. Ранее в документации к команде CREATE EVENT TRIGGER предлагалось запускать сервер в однопользовательском режиме, если ошибочно работающий триггер не удается удалить.
Новые функции to_bin и to_oct
commit: 260a1f18
К уже существующей функции to_hex для шестнадцатеричных чисел добавлены функции для перевода в двоичную и восьмеричную системы счисления:
SELECT to_bin(2), to_oct(8), to_hex(16);
to_bin | to_oct | to_hex --------+--------+-------- 10 | 10 | 10 (1 row)
См. также
Waiting for PostgreSQL 17 – Add to_bin() and to_oct(). – select * from depesz;
Новое системное представление pg_wait_events
commit: 1e68e43d
В документации к представлению pg_stat_activity для описания столбцов wait_event_type и wait_event прилагаются таблица с описанием типов событий ожидания и несколько таблиц с описанием событий ожидания каждого типа. Эти же описания теперь можно получить запросом к новому системному представлению pg_wait_events.
Это может быть удобно в системах мониторинга для получения описания ожиданий текущих процессов. Чем сейчас занят процесс контрольной точки?
SELECT a.pid, a.state, a.wait_event_type, a.wait_event, w.description FROM pg_stat_activity a JOIN pg_wait_events w ON (a.wait_event_type = w.type AND a.wait_event = w.name) WHERE a.backend_type = 'checkpointer'\gx
-[ RECORD 1 ]---+--------------------------------------------- pid | 21090 state | wait_event_type | Activity wait_event | CheckpointerMain description | Waiting in main loop of checkpointer process
EXPLAIN: счетчик времени на JIT-компиляцию преобразования кортежей
commit: 5a3423ad
В выводе EXPLAIN ANALYZE, а также в pg_stat_statements, отдельно учитывается время, затраченное на JIT-компиляцию преобразования кортежей (tuple deforming).
В следующем запросе JIT-компиляция используется для вычисления числа ?:
EXPLAIN (analyze, summary off) WITH pi AS ( SELECT random() x, random() y FROM generate_series(1,10000000) ) SELECT 4*sum(1-floor(x*x+y*y))/count(*) val FROM pi;
QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=525000.00..525000.02 rows=1 width=8) (actual time=3934.330..3934.331 rows=1 loops=1) CTE pi -> Function Scan on generate_series (cost=0.00..150000.00 rows=10000000 width=16) (actual time=725.126..1654.054 rows=10000000 loops=1) -> CTE Scan on pi (cost=0.00..200000.00 rows=10000000 width=16) (actual time=725.129..3429.925 rows=10000000 loops=1) JIT: Functions: 6 Options: Inlining true, Optimization true, Expressions true, Deforming true Timing: Generation 0.954 ms (Deform 0.161 ms), Inlining 8.476 ms, Optimization 25.632 ms, Emission 22.098 ms, Total 57.160 ms (8 rows)
Значение Generation в последней строке плана ― это суммарное время на компиляцию выражений (управляется параметром jit_expressions) и на компиляцию преобразования кортежей (jit_tuple_deforming). Сколько из этого времени заняла компиляция преобразования кортежей в 17-й версии отдельно указано в скобках (Deform).
Поскольку таблиц в этом примере нет, то можно не тратить время на компиляцию преобразования кортежей. Отключим соответствующий параметр и увидим изменения в счетчиках времени:
SET jit_tuple_deforming = off;
Часть плана запроса, касающаяся JIT:
JIT: Functions: 5 Options: Inlining true, Optimization true, Expressions true, Deforming false Timing: Generation 0.665 ms (Deform 0.000 ms), Inlining 15.209 ms, Optimization 25.897 ms, Emission 16.664 ms, Total 58.435 ms
Планировщик: уточнение оценки начальной стоимости узла WindowAgg
commit: 3900a02c
Планировщик сильно занижал стоимость получения первой строки из оконной функции в тех случаях, когда оконная функция обрабатывает большое количество строк.
В следующем, немного надуманном, примере планировщик выбирает nested loop для соединения таблиц, т. к. запрос возвращает всего одну строку, а стоимость получения первой строки очень мала:
16=> EXPLAIN (analyze, settings) SELECT t.ticket_no, SUM(tf.amount) OVER () FROM tickets t JOIN ticket_flights tf ON t.ticket_no = tf.ticket_no LIMIT 1;
QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=0.56..0.86 rows=1 width=46) (actual time=30416.237..30416.239 rows=1 loops=1) -> WindowAgg (cost=0.56..2525161.21 rows=8391708 width=46) (actual time=30416.236..30416.237 rows=1 loops=1) -> Nested Loop (cost=0.56..2420264.86 rows=8391708 width=20) (actual time=0.049..27742.105 rows=8391852 loops=1) -> Seq Scan on tickets t (cost=0.00..78913.45 rows=2949845 width=14) (actual time=0.015..287.636 rows=2949857 loops=1) -> Index Scan using ticket_flights_pkey on ticket_flights tf (cost=0.56..0.76 rows=3 width=20) (actual time=0.007..0.009 rows=3 loops=2949857) Index Cond: (ticket_no = t.ticket_no) Settings: jit = 'off', search_path = 'bookings, public' Planning Time: 0.456 ms Execution Time: 30454.862 ms (9 rows)
Нас интересует вторая строка плана с узлом WindowAgg. Начальная стоимость равна 0.56, хотя на получение первой строки ушло ~30 секунд.
В 17-й версии оценка исправлена и планировщик выбирает более уместный hash join, что сразу отражается на скорости выполнения запроса:
17=> EXPLAIN (analyze, settings) SELECT t.ticket_no, SUM(tf.amount) OVER () FROM tickets t JOIN ticket_flights tf ON t.ticket_no = tf.ticket_no LIMIT 1;
QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=523778.31..523778.31 rows=1 width=46) (actual time=7157.639..7157.642 rows=1 loops=1) -> WindowAgg (cost=523778.31..523778.36 rows=8392150 width=46) (actual time=7157.638..7157.640 rows=1 loops=1) -> Hash Join (cost=130215.56..418876.48 rows=8392150 width=20) (actual time=740.005..5075.390 rows=8391852 loops=1) Hash Cond: (tf.ticket_no = t.ticket_no) -> Seq Scan on ticket_flights tf (cost=0.00..153881.50 rows=8392150 width=20) (actual time=0.066..794.699 rows=8391852 loops=1) -> Hash (cost=78938.47..78938.47 rows=2949847 width=14) (actual time=737.972..737.973 rows=2949857 loops=1) Buckets: 262144 Batches: 32 Memory Usage: 6190kB -> Seq Scan on tickets t (cost=0.00..78938.47 rows=2949847 width=14) (actual time=0.029..307.040 rows=2949857 loops=1) Settings: search_path = 'bookings, public', jit = 'off' Planning Time: 0.331 ms Execution Time: 7202.957 ms (11 rows)
pg_constraint: ограничения NOT NULL
commit: b0e96f31
В системном каталоге pg_constraint официально появился новый, но хорошо известный, тип ограничения целостности ― NOT NULL. По разным причинам ограничения NOT NULL в pg_constraint не записывались.
Это изменение не несет новых возможностей для разработки приложений, но информация в системном каталоге становится более полной, а работа с ограничениями — более последовательной.
Ограничения NOT NULL в pg_constraint имеют тип contype = 'n':
CREATE TABLE t ( col1 int NOT NULL, col2 int NOT NULL ); SELECT conname, contype, conkey FROM pg_constraint WHERE conrelid = 't'::regclass;
conname | contype | conkey -----------------+---------+-------- t_col1_not_null | n | {1} t_col2_not_null | n | {2} (2 rows)
Ограничение можно удалить по имени, как и любое другое:
ALTER TABLE t DROP CONSTRAINT t_col2_not_null;
Увидеть ограничения можно в отдельной секции вывода команды \d+:
postgres@postgres(17.0)=# \d+ t
Table "public.t" Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description --------+---------+-----------+----------+---------+---------+-------------+--------------+------------- col1 | integer | | not null | | plain | | | col2 | integer | | | | plain | | | Not-null constraints: "t_col1_not_null" NOT NULL "col1" Access method: heap
В настоящий момент ограничения NOT NULL нельзя создать без проверки существующих данных (NOT VALID) или объявить отложенными (DEFERRED). Кроме того, ограничения NOT NULL для таблиц системного каталога в pg_constraint не записаны.
Нормализация CALL, DEALLOCATE и команд управления двухфазной фиксацией
commit: 11c34b34, 638d42a3, bb45156f
Механизм нормализации команд научился заменять параметры процедур в вызовах CALL на константы. Теперь вызов процедуры с разными параметрами будет учитываться в pg_stat_statements одной строкой:
CALL p(1); CALL p(2); CALL p(3); SELECT queryid, query, calls FROM pg_stat_statements WHERE query ILIKE 'CALL%';
queryid | query | calls ---------------------+------------+------- 7076031282516132618 | CALL p($1) | 3 (1 row)
Кроме того, на константы заменяются идентификаторы транзакций в командах управления двухфазной фиксацией (второй коммит) и имена подготовленных операторов в DEALLOCATE (третий коммит).
См. также
Нормализация DDL и служебных команд
Нормализация DDL и служебных команд, продолжение
unaccent: целевое выражение правил поддерживает значения в кавычках
commit: 59f47fb9
Для устранения диакритических знаков встроенный полнотекстовый поиск предлагает использовать словарь unaccent, поставляемый с сервером в виде одноименного расширения.
При определении собственных правил словаря целевые выражения теперь можно заключать в кавычки. Это нужно для того, чтобы целевое выражение могло начинаться с пробела или заканчиваться им. Раньше лидирующие и хвостовые пробелы обрезались.
COPY FROM: FORCE_NOT_NULL и FORCE_NULL
commit: f6d4c9cf
Параметры FORCE_NOT_NULL и FORCE_NULL команды COPY появились достаточно давно, в версиях 9.0 и 9.4 соответственно. Но требовали явного перечисления столбцов, даже если их нужно было применить ко всем столбцам таблицы.
Вместо перечисления всех столбцов теперь можно указать символ *.
CREATE TABLE t (col1 text NOT NULL, col2 text NOT NULL); COPY t FROM STDIN WITH (FORMAT csv, FORCE_NOT_NULL *);
Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself, or an EOF signal. >> "","" >> \. COPY 1
SELECT * FROM t WHERE col1 = '' and col2 = '';
col1 | col2 ------+------ | (1 row)
Аудит подключений без аутентификации
commit: e48b19c5
Если пользователь подключается без аутентификации, то при включенном параметре log_connections запись об этом появится в журнале сервера с указанием номера сработавшей строки (117) с методом «trust» в pg_hba.conf:
2023-10-19 11:40:07.609 MSK [111233] LOG: connection authenticated: user="postgres" method=trust (/home/pal/master/data/pg_hba.conf:117) 2023-10-19 11:40:07.609 MSK [111233] LOG: connection authorized: user=postgres database=demo application_name=psql
Раньше для метода «trust» в журнал записывалась только вторая строка.
Заметим, что функция SYSTEM_USER по-прежнему возвращает NULL для таких подключений.
pg_stat_subscription: новый столбец worker_type
commit: 13aeaf07
Новый столбец worker_type представления pg_stat_subscription поможет быстро определить, какую работу выполняет процесс, обслуживающий подписку. Возможные значения говорят за себя: apply, parallel apply и table synchronization.
Стоит напомнить, что сервер подписки научился применять транзакции несколькими процессами в 16-й версии.
Функция pg_promote при неуспешном переключении на реплику
commit: f593c551
Один из вариантов переключения на реплику ― это вызов функции pg_promote. Если переключиться не удалось, то функция возвращает false. И неважно, что послужило причиной:
- не уложились в таймаут на переключение,
- не получилось отправить сигнал SIGUSR1 постмастеру,
- или даже постмастер аварийно завершился в процессе переключения.
Для двух последних случаев в журнал сервера записывалось предупреждение. В 17-й версии эти предупреждения превратились в ошибки, соответственно функция pg_promote будет возвращать false только при превышении таймаута, в остальных случаях завершаться ошибкой.
Выбор метода синхронизации с диском в утилитах сервера
commit: 8c16ad3b
Утилиты сервера, пишущие файлы на диск, получили параметр --synch-method. Речь о следующих утилитах: initdb, pg_basebackup, pg_checksums, pg_dump, pg_rewind и pg_upgrade.
По умолчанию используется --synch-method=fsync.
В линукс-системах можно указать syncfs для синхронизации всей файловой системы сразу, вместо вызова fsync на каждый файл. Во многих случаях это будет быстрее. Однако возможны и побочные эффекты, если с этой же файловой системой активно работают другие приложения, изменяющие файлы: в таком случае придется дополнительно синхронизировать и их запись. В документации появилось новое приложение Appendix O. syncfs() Caveats, предупреждающее об этом.
pg_restore: оптимизация восстановления в несколько потоков большого количества таблиц
commit: c103d073, 5af0263a, 9bfd44bb
Восстановление в несколько потоков утилитой pg_restore выполнялось не оптимально при большом количестве таблиц в резервной копии. Речь о десятках тысяч таблиц и больше.
Проблема в том, что родительский процесс-координатор каждый раз сортировал список таблиц, чтобы найти самую большую и передать ее на восстановление рабочему процессу. Ресурсов на сортировку тратилось так много, что сам родительский процесс становился узким местом: рабочие процессы простаивали в ожидании работы.
Для оптимизации выбора следующей таблицы список таблиц поместили в двоичную кучу. Теперь pg_restore будет значительно быстрее восстанавливать из копий с большим количеством таблиц.
pg_basebackup и pg_receivewal с параметром dbname
commit: cca97ce6
pg_basebackup и pg_receivewal принимают параметр dbname для подключения. Вообще он не нужен этим утилитам, ведь резервные копии и WAL — это уровень всего кластера баз данных. Но в случае подключения через прокси, в частности pgbouncer, требуется указывать базу данных.
Нужно ли подключаться через pgbouncer для выполнения резервного копирования ― это отдельный вопрос. Но если другого способа подключиться нет, то данный патч дает такую возможность.
Имена параметров для ряда встроенных функций
commit: b575a26c
Название параметров необязательно при определении функции, обязателен только тип данных. Именно так и созданы большинство встроенных функций. Но если у функции более одного параметра с одинаковыми типами, то назначение параметров сложно разобрать в выводе \df, нужно заглянуть в документацию.
16=> \df string_agg
List of functions Schema | Name | Result data type | Argument data types | Type ------------+------------+------------------+---------------------+------ pg_catalog | string_agg | bytea | bytea, bytea | agg pg_catalog | string_agg | text | text, text | agg (2 rows)
В определение нескольких агрегатных функций, с более чем одним параметром, добавили название параметра. Теперь стало нагляднее:
17=> \df string_agg
List of functions Schema | Name | Result data type | Argument data types | Type ------------+------------+------------------+------------------------------+------ pg_catalog | string_agg | bytea | value bytea, delimiter bytea | agg pg_catalog | string_agg | text | value text, delimiter text | agg (2 rows)
psql: \watch min_rows
commit: f347ec76
В 16-й версии команде \watch добавили параметр count, позволяющий указать количество повторений выполнения запроса.
Теперь появился еще один способ автоматически завершить работу \watch. Если запрос вернет меньше строк, чем указано в параметре min_rows, то повторение запроса прекращается. Может быть полезным для мониторинга выполнения заданий.
CREATE TABLE job (id int, status text DEFAULT 'NEW'); INSERT INTO job VALUES (1),(2),(3);
Для удобства демонстрации обработка заданий выполняется вместе с мониторинговым запросом:
WITH process_job AS ( UPDATE job SET status = 'PROCESSED' WHERE id = (SELECT id FROM job WHERE status = 'NEW' ORDER BY id LIMIT 1) ) SELECT * FROM job WHERE status = 'NEW' \watch min_rows=1
Mon 16 Oct 2023 05:55:53 PM MSK (every 2s) id | status ----+-------- 1 | NEW 2 | NEW 3 | NEW (3 rows) Mon 16 Oct 2023 05:55:55 PM MSK (every 2s) id | status ----+-------- 2 | NEW 3 | NEW (2 rows) Mon 16 Oct 2023 05:55:57 PM MSK (every 2s) id | status ----+-------- 3 | NEW (1 row) Mon 16 Oct 2023 05:55:59 PM MSK (every 2s) id | status ----+-------- (0 rows)
На этом пока всё. Ждем ноябрьского коммитфеста 17-й версии.
