25.06.2025

Расширение auto_dump

Для расследования проблем с пакетными запросами в СУБД PostgreSQL , начиная с версий

PostgreSQL 17.5-3.1C
PostgreSQL 16.9-16.1C
PostgreSQL 17.4-10.1C
PostgreSQL 16.8-40.1C

было добавлено новое расширение auto_dump.

Данное расширение помогает упростить процесс получения воспроизведения медленного выполнения запроса без использования платформы 1С:Предприятие. Расширение:

Формирует выгрузку таблиц, которые используются в запросе. Можно включать в выгрузку, как временные таблицы, так и постоянные таблицы;
Создает sql-запрос для переноса и подготовки данных для воспроизведения проблемного запроса;
Создает файл проблемного sql-запроса для его воспроизведения и получает его план выполнения.

Параметры для настройки расширения.

Общие параметры

auto_dump.enable – булево

Признак включения расширения. По умолчанию расширение выключено.

auto_dump.output_directory - строка

Путь директории, в которую будут сохраняться файлы с дампами таблиц. Параметр обязателен для настройки. У пользователя, под которым запущена служба СУБД, должны быть права на доступ к этой директории.

Параметры, включающие критерии срабатывания

auto_dump.dump_on_query_string - строка

Фрагмент sql-запроса, при появлении которого в запросе будет осуществлено срабатывание получения дампа.

auto_dump.dump_on_cancel - булево

При включении параметра будет осуществляться дамп запросов отмененных со стороны СУБД (например, командой pg_cancel_backend или в момент lock timeout). По умолчанию параметр выключен.

auto_dump.dump_on_bad_plan – булево

При включении параметра будет анализироваться ожидаемое и фактическое количество строк для всех запросов. Чтобы сработало автоматическое формирование дампа по запросу, должны одновременно выполниться два критерия auto_dump.bad_plan_percent_threshold и auto_dump.bad_plan_count_threshold, которые определяют запрос как «проблемный». По умолчанию параметр выключен.

auto_dump.bad_plan_count_threshold – число

Критерий срабатывания автоматического дампа по абсолютной разнице между ожидаемым и фактическим количеством строк.
Анализируются все узлы плана. Если ожидаемое количество строк отличается от фактического количества строк более чем на заданное в параметре число, то критерий считается выполнен.
Если значение равно 0, то критерий считается выполненным всегда.
По умолчанию параметр установлен 0.

auto_dump.bad_plan_percent_threshold – число

Критерий срабатывания автоматического дампа по разнице в процентах между ожидаемым и фактическим количеством строк.
В качестве параметра устанавливается число от 0 до 100. Анализируются все узлы плана. По каждому узлу проверяется отношение суммы ожидаемого количества строк и фактического количества строк к ожидаемому количеству строк. Если это отношение превышает установленный параметр, то критерий считается выполненным.
Если значение равно 0, то критерий считается выполненным всегда.
По умолчанию параметр установлен 0.

Параметры, определяющие объём данных в дампе

auto_dump.dump_temporary_tables – булево

При включении параметра в дамп будут добавлены временные таблицы сессии, участвующие в текущем запросе.
По умолчанию включено.

auto_dump.dump_persistent_tables – булево

При включении параметра в дамп будут добавлены все постоянные таблицы, участвующие в текущем запросе.
По умолчанию выключено.

auto_dump.dump_all_temp_tables – булево

При включении параметра в дамп будут добавлены все временные таблицы сессии, в том числе и участвующие в текущем запросе.
По умолчанию выключено.

auto_dump.dump_data – булево

При включении параметра в дамп будет записано содержимое таблиц, для которых снимается дамп. Если параметр выключен, то в sql-запросе будет описано только создание таблиц, без их заполнения.
По умолчанию включено.

auto_dump.dump_indexes – булево

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

auto_dump.dump_query – булево

При включении параметра будет сформирован файл с sql-запросом, для которого сформирован дамп.
По умолчанию включено.

auto_dump.dump_create – булево

При включении параметра будет сформирован файл с sql-командами создания таблиц, для которых снимается дамп.
По умолчанию включено.

auto_dump.dump_plan - булево

При включении параметра будет сформирован файл с планом выполнения текущего SQL-запроса.
По умолчанию включено.

Включение расширения

Для работы расширения необходимо добавить его в конфигурационном файле postgresql.conf

shared_preload_libraries = ‘auto_dump’

Затем описать необходимые параметры расширения и перезапустить службу СУБД.

Через утилиту psql необходимо подключиться к базе данных и создать в ней расширение

Копировать в буфер обмена

user@server: psql -h localhost -U postgres -d myDB
myDB =# CREATE EXTENSION auto_dump;

Пример использования.

С помощью технологического журнала по событию DBPOSTGRS был получен текст sql-запроса

Копировать в буфер обмена

SELECT
T3._Q_001_F_000RRef,
T3._Q_001_F_001,
T1._Q_001_F_002,
T1._Q_001_F_001RRef,
T2._Fld23
FROM pg_temp.tt3 T1
LEFT OUTER JOIN _Reference7X1 T2
ON (T1._Q_001_F_001RRef = T2._IDRRef)
INNER JOIN pg_temp.tt2 T3
ON (T1._Q_001_F_000RRef = T3._Q_001_F_000RRef)

В конфигурационном файле postgresql.conf были добавлены строки:

Копировать в буфер обмена

shared_preload_libraries = ‘auto_dump’
auto_dump.enable = on
auto_dump.output_directory = ‘/tmp/auto_d’
auto_dump.dump_on_query_string = ‘LEFT OUTER JOIN _Reference7X1’

Перезапуск службы postgresql

Копировать в буфер обмена

user@server: sudo systemctl restart postgresql@16-main.service

Добавление расширения в базу данных auto_d

Копировать в буфер обмена

user@server: psql -h localhost -U postgres -d auto_d
auto_d=# create extension auto_dump;
CREATE EXTENSION

Пользователь в 1С выполнил действие, которое использует проблемный запрос.
После этого в каталоге ‘/tmp/auto_d’ появился новый каталог, который имеет следующий формат “PIDПроцесса-ГГГГ-ММ-ДД-ЧЧ-ММ-СС-ПорядковыйНомер”

Копировать в буфер обмена

user@server:/tmp/auto_d/71491-2025_06_11_12_42_36_00$ ls -l
-rw------- 1 postgres postgres 3241 июн 11 12:42 create.sql
-rw------- 1 postgres postgres 6743 июн 11 12:42 createwithdata.sql

Файл create.sql содержит текст:

Копировать в буфер обмена

CREATE TEMPORARY TABLE tt3 (
    _q_001_f_000rref bytea,
    _q_001_f_001rref bytea,
    _q_001_f_002 numeric(22,2)
);

CREATE INDEX tmpind_1 ON pg_temp.tt3 USING btree (_q_001_f_000rref);

ANALYZE tt3;

CREATE TEMPORARY TABLE tt2 (
    _q_001_f_000rref bytea,
    _q_001_f_001 numeric(16,2)
);

CREATE INDEX tmpind_0 ON pg_temp.tt2 USING btree (_q_001_f_000rref);

ANALYZE tt2;

/*
SELECT
T3._Q_001_F_000RRef,
T3._Q_001_F_001,
T1._Q_001_F_002,
T1._Q_001_F_001RRef,
T2._Fld23
FROM pg_temp.tt3 T1
LEFT OUTER JOIN _Reference7X1 T2
ON (T1._Q_001_F_001RRef = T2._IDRRef)
INNER JOIN pg_temp.tt2 T3
ON (T1._Q_001_F_000RRef = T3._Q_001_F_000RRef);

*/
/*
Query Text: SELECT
T3._Q_001_F_000RRef,
T3._Q_001_F_001,
T1._Q_001_F_002,
T1._Q_001_F_001RRef,
T2._Fld23
FROM pg_temp.tt3 T1
LEFT OUTER JOIN _Reference7X1 T2
ON (T1._Q_001_F_001RRef = T2._IDRRef)
INNER JOIN pg_temp.tt2 T3
ON (T1._Q_001_F_000RRef = T3._Q_001_F_000RRef)
Hash Join (cost=8.01..9.51 rows=22 width=79)
Hash Cond: (t1._q_001_f_000rref = t3._q_001_f_000rref)
-> Hash Left Join (cost=6.76..8.01 rows=22 width=72)
        Hash Cond: (t1._q_001_f_001rref = t2._idrref)
        -> Seq Scan on tt3 t1 (cost=0.00..1.22 rows=22 width=40)
        -> Hash (cost=6.36..6.36 rows=36 width=64)
              -> Seq Scan on _reference7x1 t2 (cost=0.00..6.36 rows=36 width=64)
-> Hash (cost=1.12..1.12 rows=12 width=24)
        -> Seq Scan on tt2 t3 (cost=0.00..1.12 rows=12 width=24)
*/
/*
Query Text: SELECT
T3._Q_001_F_000RRef,
T3._Q_001_F_001,
T1._Q_001_F_002,
T1._Q_001_F_001RRef,
T2._Fld23
FROM pg_temp.tt3 T1
LEFT OUTER JOIN _Reference7X1 T2
ON (T1._Q_001_F_001RRef = T2._IDRRef)
INNER JOIN pg_temp.tt2 T3
ON (T1._Q_001_F_000RRef = T3._Q_001_F_000RRef)
Hash Join (cost=8.01..9.51 rows=22 width=79) (actual time=0.027..0.037 rows=22 loops=1)
Output: t3._q_001_f_000rref, t3._q_001_f_001, t1._q_001_f_002, t1._q_001_f_001rref, t2._fld23
Hash Cond: (t1._q_001_f_000rref = t3._q_001_f_000rref)
Buffers: shared hit=6, local hit=2
-> Hash Left Join (cost=6.76..8.01 rows=22 width=72) (actual time=0.020..0.025 rows=22 loops=1)
        Output: t1._q_001_f_002, t1._q_001_f_001rref, t1._q_001_f_000rref, t2._fld23
        Inner Unique: true
        Hash Cond: (t1._q_001_f_001rref = t2._idrref)
        Buffers: shared hit=6, local hit=1
        -> Seq Scan on pg_temp.tt3 t1 (cost=0.00..1.22 rows=22 width=40) (actual time=0.001..0.002 rows=22 loops=1)
              Output: t1._q_001_f_000rref, t1._q_001_f_001rref, t1._q_001_f_002
              Buffers: local hit=1
        -> Hash (cost=6.36..6.36 rows=36 width=64) (actual time=0.016..0.016 rows=36 loops=1)
              Output: t2._fld23, t2._idrref
              Buckets: 1024 Batches: 1 Memory Usage: 11kB
              Buffers: shared hit=6
              -> Seq Scan on public._reference7x1 t2 (cost=0.00..6.36 rows=36 width=64) (actual time=0.003..0.011 rows=36 loops=1)
                    Output: t2._fld23, t2._idrref
                    Buffers: shared hit=6
-> Hash (cost=1.12..1.12 rows=12 width=24) (actual time=0.005..0.005 rows=12 loops=1)
        Output: t3._q_001_f_000rref, t3._q_001_f_001
        Buckets: 1024 Batches: 1 Memory Usage: 9kB
        Buffers: local hit=1
        -> Seq Scan on pg_temp.tt2 t3 (cost=0.00..1.12 rows=12 width=24) (actual time=0.002..0.002 rows=12 loops=1)
              Output: t3._q_001_f_000rref, t3._q_001_f_001
              Buffers: local hit=1
Settings: enable_mergejoin = 'off', cpu_operator_cost = '0.001'
*/

Файл createwithdata.sql содержит текст:

Копировать в буфер обмена

CREATE TEMPORARY TABLE tt3 (
    _q_001_f_000rref bytea,
    _q_001_f_001rref bytea,
    _q_001_f_002 numeric(22,2)
);

CREATE INDEX tmpind_1 ON pg_temp.tt3 USING btree (_q_001_f_000rref);

INSERT INTO tt3 (_q_001_f_000rref, _q_001_f_001rref, _q_001_f_002) VALUES
    (E'\\x8ca0000d8843cd1b11dc8d043d710079',E'\\x8ca1000d8843cd1b11dc8ea92d97a749',36960.00),
….
    (E'\\x8ca1000d8843cd1b11dc8ea92d97a74a',E'\\x8ca1000d8843cd1b11dc8ea92d97a749',123750.00);

ANALYZE tt3;

CREATE TEMPORARY TABLE tt2 (
    _q_001_f_000rref bytea,
    _q_001_f_001 numeric(16,2)
);

CREATE INDEX tmpind_0 ON pg_temp.tt2 USING btree (_q_001_f_000rref);

INSERT INTO tt2 (_q_001_f_000rref, _q_001_f_001) VALUES
    (E'\\x8ca0000d8843cd1b11dc8d043d71007d',59500.00),
…
    (E'\\x8ca0000d8843cd1b11dc8d043d710076',153176.73);

ANALYZE tt2;

/*
SELECT
T3._Q_001_F_000RRef,
T3._Q_001_F_001,
T1._Q_001_F_002,
T1._Q_001_F_001RRef,
T2._Fld23
FROM pg_temp.tt3 T1
LEFT OUTER JOIN _Reference7X1 T2
ON (T1._Q_001_F_001RRef = T2._IDRRef)
INNER JOIN pg_temp.tt2 T3
ON (T1._Q_001_F_000RRef = T3._Q_001_F_000RRef);

*/
/*
Query Text: SELECT
T3._Q_001_F_000RRef,
T3._Q_001_F_001,
T1._Q_001_F_002,
T1._Q_001_F_001RRef,
T2._Fld23
FROM pg_temp.tt3 T1
LEFT OUTER JOIN _Reference7X1 T2
ON (T1._Q_001_F_001RRef = T2._IDRRef)
INNER JOIN pg_temp.tt2 T3
ON (T1._Q_001_F_000RRef = T3._Q_001_F_000RRef)
Hash Join (cost=8.01..9.51 rows=22 width=79)
Hash Cond: (t1._q_001_f_000rref = t3._q_001_f_000rref)
-> Hash Left Join (cost=6.76..8.01 rows=22 width=72)
        Hash Cond: (t1._q_001_f_001rref = t2._idrref)
        -> Seq Scan on tt3 t1 (cost=0.00..1.22 rows=22 width=40)
        -> Hash (cost=6.36..6.36 rows=36 width=64)
              -> Seq Scan on _reference7x1 t2 (cost=0.00..6.36 rows=36 width=64)
-> Hash (cost=1.12..1.12 rows=12 width=24)
        -> Seq Scan on tt2 t3 (cost=0.00..1.12 rows=12 width=24)
*/
/*
Query Text: SELECT
T3._Q_001_F_000RRef,
T3._Q_001_F_001,
T1._Q_001_F_002,
T1._Q_001_F_001RRef,
T2._Fld23
FROM pg_temp.tt3 T1
LEFT OUTER JOIN _Reference7X1 T2
ON (T1._Q_001_F_001RRef = T2._IDRRef)
INNER JOIN pg_temp.tt2 T3
ON (T1._Q_001_F_000RRef = T3._Q_001_F_000RRef)
Hash Join (cost=8.01..9.51 rows=22 width=79) (actual time=0.027..0.037 rows=22 loops=1)
Output: t3._q_001_f_000rref, t3._q_001_f_001, t1._q_001_f_002, t1._q_001_f_001rref, t2._fld23
Hash Cond: (t1._q_001_f_000rref = t3._q_001_f_000rref)
Buffers: shared hit=6, local hit=2
-> Hash Left Join (cost=6.76..8.01 rows=22 width=72) (actual time=0.020..0.025 rows=22 loops=1)
        Output: t1._q_001_f_002, t1._q_001_f_001rref, t1._q_001_f_000rref, t2._fld23
        Inner Unique: true
        Hash Cond: (t1._q_001_f_001rref = t2._idrref)
        Buffers: shared hit=6, local hit=1
        -> Seq Scan on pg_temp.tt3 t1 (cost=0.00..1.22 rows=22 width=40) (actual time=0.001..0.002 rows=22 loops=1)
              Output: t1._q_001_f_000rref, t1._q_001_f_001rref, t1._q_001_f_002
              Buffers: local hit=1
        -> Hash (cost=6.36..6.36 rows=36 width=64) (actual time=0.016..0.016 rows=36 loops=1)
              Output: t2._fld23, t2._idrref
              Buckets: 1024 Batches: 1 Memory Usage: 11kB
              Buffers: shared hit=6
              -> Seq Scan on public._reference7x1 t2 (cost=0.00..6.36 rows=36 width=64) (actual time=0.003..0.011 rows=36 loops=1)
                    Output: t2._fld23, t2._idrref
                    Buffers: shared hit=6
-> Hash (cost=1.12..1.12 rows=12 width=24) (actual time=0.005..0.005 rows=12 loops=1)
        Output: t3._q_001_f_000rref, t3._q_001_f_001
        Buckets: 1024 Batches: 1 Memory Usage: 9kB
        Buffers: local hit=1
        -> Seq Scan on pg_temp.tt2 t3 (cost=0.00..1.12 rows=12 width=24) (actual time=0.002..0.002 rows=12 loops=1)
              Output: t3._q_001_f_000rref, t3._q_001_f_001
              Buffers: local hit=1
Settings: enable_mergejoin = 'off', cpu_operator_cost = '0.001'
*/