Отслеживание времени изменения объекта СУБД

Описание

Сведения

Функциональность доступна только для редакций Enterprise и Enterprise для ERP-систем.

Описание

В целях повышения удобства сопровождения СУБД, при выявлении причин снижения ее производительности, вызванного выполнением DDL-операций над объектами БД, предоставляется инструмент в виде двух представлений psql_dba_objects и psql_all_objects, которые используются для отслеживания даты и времени последней модификации объектов СУБД.

Настройка

Включение функциональности

Для включения или отключения функциональности предназначен конфигурационный параметр enable_monitor_object_modification_date (в файле postgresql.conf). Значение параметра по умолчанию - on. Для изменения параметра необходим перезапуск сервера СУБД Pangolin.

Объекты системного каталога

Для хранения времени изменения объектов БД добавляются 2 таблицы системного каталога (в схеме pg_catalog):

• psql_omd (omd - object modification date - дата изменения объекта) - для хранения времени создания, удаления и последней модификации объектов БД;
• psql_omd_shared - для хранения времени создания, удаления и последней модификации глобальных объектов в кластере.

Столбцы таблиц psql_omd и psql_omd_shared:

Имя столбца	Тип	Описание
objoid	oid	OID объекта БД
classoid	oid	OID системного каталога, в котором содержится объект БД
created	timestamp with time zone	Дата и время создания объекта БД (может быть NULL)
last_ddl_time	timestamp with time zone	Дата и время последней модификации логической структуры объекта БД
deleted	timestamp with time zone	Дата и время удаления объекта БД (может быть NULL)
name	name	Имя объекта БД (может быть NULL, устанавливается только для удаленных объектов)
namespace	oid	OID пространства имен, содержащего объект БД (может быть NULL, устанавливается только для удаленных объектов)
type	char	Тип объекта БД (может быть NULL, устанавливается только для удаленных объектов): r — таблица; T — временная таблица; S — последовательность; i — индекс; t — TOAST-таблица; v — представление; m — материализованное представление; c — составной тип; f — внешняя таблица; p — партиционированная таблица; I — индекс для партиционированной таблицы; H — гипертаблица TimescaleDB; h — чанк гипертаблицы TimescaleDB; F — функция; P — процедура; w — оконная функция; a — агрегатная функция; R — роль; G — триггер

Для таблиц psql_omd и psql_omd_shared создаются уникальные B-Tree индексы по столбцам objoid и classoid: psql_omd_oid_index и psql_omd_shared_oid_index соответственно.

Для отображения времени изменения объектов БД существуют два представления в схеме pg_catalog:

• psql_dba_objects - для получения списка всех объектов с информацией о датах и времени создания, удаления и последнем изменении;

  Запрос представления pg_dba_objects

  CREATE OR REPLACE VIEW psql_dba_objects AS
  SELECT
      o.objoid AS oid,
  
      CASE
          WHEN o.deleted IS NULL THEN
              CASE
                  WHEN c.oid IS NOT NULL THEN c.relname
                  WHEN p.oid IS NOT NULL THEN p.proname
                  WHEN a.oid IS NOT NULL THEN a.rolname
                  WHEN t.oid IS NOT NULL THEN t.tgname
              END
          ELSE o.name
      END AS name,
  
      CASE
          WHEN o.deleted IS NULL THEN
              CASE
                  WHEN c.oid IS NOT NULL THEN nc.oid
                  WHEN p.oid IS NOT NULL THEN np.oid
              END
          ELSE o.namespace
      END AS namespace,
  
      CASE
          WHEN o.deleted IS NULL THEN
              CASE
                  WHEN c.oid IS NOT NULL THEN
                      CASE
                          WHEN c.relkind = 'r' THEN
                              CASE
                                  WHEN is_table_hypertable_or_chunk(c.oid, false) THEN 'hypertable'
                                  WHEN is_table_hypertable_or_chunk(c.oid, true) THEN 'chunk'
                                  WHEN c.relpersistence = 't' THEN 'temporary table'
                                  ELSE 'table'
                              END
                          WHEN c.relkind = 'S' THEN 'sequence'
                          WHEN c.relkind = 'i' THEN 'index'
                          WHEN c.relkind = 't' THEN 'toast table'
                          WHEN c.relkind = 'v' THEN 'view'
                          WHEN c.relkind = 'm' THEN 'materialized view'
                          WHEN c.relkind = 'c' THEN 'composite type'
                          WHEN c.relkind = 'f' THEN 'foreign table'
                          WHEN c.relkind = 'p' THEN 'partitioned table'
                          WHEN c.relkind = 'I' THEN 'partitioned index'
                      END
                  WHEN p.oid IS NOT NULL THEN
                      CASE
                          WHEN p.prokind = 'f' THEN 'function'
                          WHEN p.prokind = 'p' THEN 'procedure'
                          WHEN p.prokind = 'w' THEN 'window'
                          WHEN p.prokind = 'a' THEN 'aggregate'
                      END
                  WHEN a.oid IS NOT NULL THEN 'role'
                  WHEN t.oid IS NOT NULL THEN 'trigger'
              END
          ELSE
              CASE
                  WHEN o.type = 'r' THEN 'table'
                  WHEN o.type = 'T' THEN 'temporary table'
                  WHEN o.type = 'S' THEN 'sequence'
                  WHEN o.type = 'i' THEN 'index'
                  WHEN o.type = 't' THEN 'toast table'
                  WHEN o.type = 'v' THEN 'view'
                  WHEN o.type = 'm' THEN 'materialized view'
                  WHEN o.type = 'c' THEN 'composite type'
                  WHEN o.type = 'f' THEN 'foreign table'
                  WHEN o.type = 'p' THEN 'partitioned table'
                  WHEN o.type = 'I' THEN 'partitioned index'
                  WHEN o.type = 'H' THEN 'hypertable'
                  WHEN o.type = 'h' THEN 'chunk'
                  WHEN o.type = 'F' THEN 'function'
                  WHEN o.type = 'P' THEN 'procedure'
                  WHEN o.type = 'w' THEN 'window'
                  WHEN o.type = 'a' THEN 'aggregate'
                  WHEN o.type = 'R' THEN 'role'
                  WHEN o.type = 'G' THEN 'trigger'
              END
      END AS type,
  
      (SELECT rolname FROM pg_authid ai
          WHERE ai.oid =
              CASE
                  WHEN c.oid IS NOT NULL THEN c.relowner
                  WHEN p.oid IS NOT NULL THEN p.proowner
              END)
      AS owner,
  
      o.objoid < 16384 AS ispredef,
  
      o.created,
  
      o.last_ddl_time,
  
      o.deleted
  
  FROM (
      SELECT objoid, created, deleted, last_ddl_time, name, namespace, type FROM psql_omd
      UNION
      SELECT objoid, created, deleted, last_ddl_time, name, namespace, type FROM psql_omd_shared) o
  LEFT JOIN (pg_class c JOIN pg_namespace nc ON (c.relnamespace = nc.oid)) ON o.objoid = c.oid
  LEFT JOIN (pg_proc p JOIN pg_namespace np ON (p.pronamespace = np.oid)) ON o.objoid = p.oid
  LEFT JOIN pg_authid a ON o.objoid = a.oid
  LEFT JOIN pg_trigger t ON o.objoid = t.oid;

• psql_all_objects - для получения списка объектов, принадлежащих вызывающему пользователю, с информацией о датах и времени создания, удаления и последней модификации. Владелец объекта определяется по идентификатору пользователя сеанса (SESSION_USER).

  Запрос представления pg_all_objects

  CREATE OR REPLACE VIEW psql_all_objects AS
  SELECT * FROM psql_dba_objects
      WHERE owner = session_user OR
            type = 'role' OR
            type = 'trigger';

Столбцы представлений psql_dba_objects и psql_all_objects:

Имя столбца	Тип	Описание
oid	oid	OID объекта БД
name	name	Имя объекта БД
namespace	oid	OID пространства имен, содержащего объект БД
type	text	Тип объекта БД: table (таблица); toast table (TOAST-таблица); temporary table (временная таблица); index (индекс); partitioned table (партиционированная таблица); partitioned index (индекс для партиционированной таблицы); foreign table (внешняя таблица); hypertable (гипертаблица TimescaleDB); chunk (чанк гипертаблицы TimescaleDB); view (представление); materialized view (материализованное представление); sequence (последовательность); composite type (составной тип); function (функция); procedure (процедура); aggregate (агрегатная функция); window (оконная функция); role (роль); trigger (триггер);
owner	name	Владелец объекта БД
ispredef	boolean	Флаг предустановленности объекта БД — был ли объект создан в ходе первичной инициализации кластера
created	timestamp with time zone	Дата и время создания объекта БД (может быть NULL)
deleted	timestamp with time zone	Дата и время удаления объекта БД (может быть NULL)
last_ddl_time	timestamp with time zone	Дата и время последней модификации логической структуры объекта БД

Функции

Существуют две функции для работы с объектами:

• is_table_hypertable_or_chunk(relid oid, is_chunk boolean) → boolean для отображения типа объекта hypertable (гипертаблица) или chunk (фрагмент гипертаблицы). Функция принимает идентификатор отношения (relid) и флаг is_chunk.

  Если флаг равен false, функция проверяет, является ли объект гипертаблицей, если true — проверяет, является ли он фрагментом гипертаблицы (chunk). Функция возвращает true, если тип отношения, в зависимости от флага is_chunk, является гипертаблицей или сегментом (chunk), иначе - false.

• purge_object_mod_dates(start_time timestamptz, end_time timestamptz) → void для очистки записей об объектах, чьи дата и время удаления попадают во входной интервал времени [start_time, end_time]. Аргументы функции могут иметь значение NULL, что эквивалентно '-infinity' для start_time и 'infinity' для end_time.

Инициализация кластера БД

При инициализации кластера (initdb) для каждой БД создаются свои экземпляры таблицы psql_omd и представлений psql_dba_objects, psql_all_objects. Таблица psql_omd_shared создается в одном экземпляре и разделяется всеми БД в кластере.

Заполнение таблиц psql_omd и psql_omd_shared для предустановленных объектов осуществляется при их создании посредством команд BKI, выполняемых при запуске СУБД Pangolin утилитой initdb в режиме bootstrap (первого запуска).

Предустановленные объекты перечисляются в .dat-файлах, связанных с системными каталогами pg_class, pg_proc, pg_authid и pg_trigger. В объявлении структур перечисленных системных каталогов добавляется макрос BKI_TRACK_OMD. Команды BKI формируются для каждой записи в .dat-файле каталога, отмеченного макросом BKI_TRACK_OMD, в процессе сборки скриптом src/backend/catalog/genbki.pl.

примечание

Количество записей в каталоге psql_omd_shared не изменяется на всем времени жизни кластера, поскольку глобальные объекты запрещено создавать и удалять после инициализации кластера.

Заполнение таблицы psql_omd для остальных предустановленных объектов выполняется в обработчиках DDL-команд, при запуске СУБД Pangolin, утилитой initdb в однопользовательском режиме.

Управление

Создание новой БД

Новая БД создается копированием шаблонной БД. При этом метки времени создания и последней модификации объектов шаблонной БД наследуются, поэтому в новой БД эти метки времени нужно обновить. Для этого рабочий процесс, выполняющий создание БД, регистрирует фоновый рабочий процесс и дожидается его запуска. Через глобальную переменную MyBgworkerEntry в фоновый процесс передаются номер транзакции, в рамках которой создаются БД и параметры для подключения к ней: OID БД и OID роли, создающей БД. Фоновый процесс дожидается фиксации или отката транзакции, создающей БД. Если транзакция зафиксирована, фоновый процесс выполняет подключение к новой БД и обновляет метки времени для каждого объекта новой БД, после чего завершается.

Права доступа

Таблицы psql_omd и psql_omd_shared доступны пользователям с правами superuser только на чтение.

Представление psql_dba_objects доступно пользователям с правами superuser, а также групповой роли pg_monitor, но только на чтение.

Представление psql_all_objects доступно всем только на чтение.

Функция purge_object_mod_dates(start_time timestamptz, end_time timestamptz) → void доступна на исполнение пользователям с правами superuser.

Функция is_table_hypertable_or_chunk(relid oid, is_chunk boolean) → boolean доступна на исполнение всем.

примечание

В однопользовательском режиме доступ на изменение таблиц psql_omd и psql_omd_shared остается разрешенным.

Установка даты и времени создания, удаления и последней модификации объектов БД

При создании объекта в БД в системный каталог psql_omd добавляется запись с датой и временем создания (поле created) и последней модификации (поле last_ddl_time) соответствующего объекта. При этом значения полей created и last_ddl_time совпадают.

примечание

Обновление поля last_ddl_time осуществляется в обработчиках DDL-команд, изменяющих логическую структуру объекта, а так же GRANT/REVOKE. В обработчиках остальных DDL-команд, не модифицирующих логическую структуру объекта, значение поля не меняется. К таким DDL-командам относятся:

• REINDEX;
• CLUSTER;
• TRUNCATE;
• VACUUM;
• VACUUM FULL;
• REFRESH MATERIALIZED VIEW [CONCURRENTLY].

Если объект глобальный для всего кластера, то обновление поля last_ddl_time выполняется в каталоге psql_omd_shared, иначе - в каталоге psql_omd.

Установка поля deleted происходит при удалении соответствующего объекта из БД. Поскольку запись об объекте удаляется из системных каталогов pg_class, pg_proc, pg_authid или pg_trigger в зависимости от типа объекта, то в запросах представлений psql_dba_objects и psql_all_objects исчезает источник информации об имени, схеме и типе объекта, поэтому эти данные сохраняются в полях name, namespace и type в таблицах psql_omd и psql_omd_shared.

Следующие функции администраторов безопасности рассматриваются как DDL-операции в отношении объектов, на которые они воздействуют, поэтому их выполнение обновляет дату последней модификации соответствующих объектов:

• pm_create_security_admin();
• pm_set_security_admin_password();
• pm_unblock_security_admin();
• pm_revoke_security_admin();
• pm_assign_policy_to_user();
• pm_unassign_policy_from_user();
• pm_suspend_object();
• pm_resume_object();
• pm_unprotect_object();
• pm_protect_object();
• pm_grant_security_admin().

Функции парольных политик трактуются как DDL-операции по отношению к пользователю, поэтому их вызовы приводят к обновлению даты последней модификации пользователя:

• enable_policy();
• enable_policy_by_id();
• disable_policy();
• disable_policy_by_id();
• set_role_policies();
• set_role_policies_by_id().

Все перечисленные изменения в таблицах psql_omd и psql_omd_shared применяются в момент фиксации транзакции, а для подготовленных транзакций — при выполнении операции подготовки (PREPARE TRANSACTION).

Для очистки записей о датах изменения удаленных объектов используется функция purge_object_mod_dates([start_time timestamptz, end_time timestamptz]) → void.

Функция принимает на вход начало и конец интервала времени, в пределах которого требуется очистить записи объектов, даты удаления которых попадают в этот интервал. Входные аргументы могут быть опущены, тогда функция удалит записи о всех удаленных объектах за все время существования БД.

В качестве неопределенной границы интервала времени может использоваться значение NULL. Вызов упрощенной вариации функции без аргументов purge_object_mod_dates() равнозначен вызову purge_object_mod_dates(NULL, NULL) и purge_object_mod_dates('-infinity', 'infinity').

Пример вызова функции purge_object_mod_dates():

SELECT purge_object_mod_dates('2025-06-08 09:24:51'::timestamptz, '2025-06-08 10:24:51'::timestamptz);
SELECT purge_object_mod_dates('-infinity', '2025-06-08 10:24:51'::timestamptz);
SELECT purge_object_mod_dates('2025-06-08 09:24:51'::timestamptz, 'infinity');
SELECT purge_object_mod_dates('-infinity', 'infinity');
SELECT purge_object_mod_dates(NULL, '2025-06-08 10:24:51'::timestamptz);
SELECT purge_object_mod_dates('2025-06-08 09:24:51'::timestamptz, NULL);
SELECT purge_object_mod_dates(NULL, NULL);
SELECT purge_object_mod_dates();
SELECT purge_object_mod_dates(now() - interval '1 day', now());

Для сохранения информации о датах изменения удаленных объектов, в течение некоторого интервала времени, введен параметр object_modification_date_keep_interval. В пределах этого интервала времени функции очистки запрещено удалять записи с датами изменения удаленных объектов, которые входят в этот интервал. Интервал отсчитывается от момента выполнения функции очистки psql_purge_object_mod_dates(). Параметр принимает неотрицательные значения типа interval. Если указан нулевой интервал (0), то ограничения на выполнение функции очистки не накладываются. Значение по умолчанию - 1 week (1 неделя). Для изменения параметра необходим перезапуск сервера Pangolin. Дополнительно параметр вынесен в пользовательский конфигурационный файл автоматизированного развертывания для настройки на этапе установки.

Внимание!

Может потребоваться увеличить параметр max_worker_processes, чтобы это число включало дополнительные рабочие процессы, обновляющие даты создания и модификации объектов в новой БД после ее создания.

Влияние на потоковую и логическую репликации

При выполнении DDL-команд ожидается увеличение объема журналов и трафика между узлами кластера, поскольку фиксация изменений объектов выполняется в системных каталогах, а это, в свою очередь, приводит к увеличению количества WAL-записей XLOG_HEAP_INSERT, XLOG_HEAP_UPDATE и XLOG_HEAP_DELETE.

Сценарии использования

Примеры запросов представлений

Пример выполнения запроса представления psql_dba_objects:

SELECT * FROM pg_catalog.psql_dba_objects;

Пример вывода:

-[ RECORD 1 ]-+------------------------------
oid           | 16384
name          | t1
namespace     | 2200
type          | table
owner         | postgres
ispredef      | f
created       | 2022-05-31 12:58:20.765479+03
last_ddl_time | 2022-05-31 12:58:20.765479+03
deleted       |

Пример выполнения запроса представления psql_all_objects:

SELECT * FROM pg_catalog.psql_all_objects;

Пример вывода:

-[ RECORD 1 ]-+------------------------------
oid           | 16384
name          | t1
namespace     | 2200
type          | table
owner         | postgres
ispredef      | f
created       | 2022-05-31 12:58:20.765479+03
last_ddl_time | 2022-05-31 12:58:20.765479+03
deleted       |

Отслеживание времени создания, удаления и последней модификации объекта типа таблица

Перед выполнением сценария установите параметр object_modification_date_keep_interval в значение 0, чтобы функция purge_object_mod_dates() очищала записи с датами модификации удаленных объектов и перезапустите сервер Pangolin.

1. Создайте таблицу:

   CREATE TABLE t1 (i INT, t TEXT);

   Успешный результат: Таблица создана.

2. Проверьте наличие таблицы в представлении psql_dba_objects и что дата последней модификации таблицы совпадает с датой создания:

   SELECT created, deleted, last_ddl_time FROM psql_dba_objects
   WHERE name = 't1' AND
        type = 'table' AND
        ispredef = 'f';

   Пример успешного выполнения запроса: Запись о таблице присутствует в представлении psql_dba_objects и дата последней модификации таблицы совпадает с датой создания:

   -[ RECORD 1 ]-+------------------------------
   created       | 2025-09-12 13:26:42.994842+03
   deleted       |
   last_ddl_time | 2025-09-12 13:26:42.994842+03

3. Выполните DDL-команду над таблицей, например:

   ALTER TABLE t1 RENAME COLUMN i TO a;

   Успешный результат: Таблица изменена.

4. Проверьте, что дата последней модификации таблицы обновлена, а дата создания осталась прежней:

   SELECT created, deleted, last_ddl_time FROM psql_dba_objects
   WHERE name = 't1' AND
        type = 'table' AND
        ispredef = 'f';

   Пример успешного выполнения запроса: Дата последней модификации таблицы обновлена, а дата создания осталась прежней:

   -[ RECORD 1 ]-+------------------------------
   created       | 2025-09-12 13:26:42.994842+03
   deleted       |
   last_ddl_time | 2025-09-12 13:30:35.061483+03

5. Удалите таблицу t1:

   DROP TABLE t1;

   Успешный результат: Таблица удалена.

6. Проверьте, что дата удаления таблицы установлена:

   SELECT created, deleted, last_ddl_time FROM psql_dba_objects
   WHERE name = 't1' AND
        type = 'table' AND
        ispredef = 'f';

   Пример успешного выполнения запроса: Дата удаления таблицы установлена:

   -[ RECORD 1 ]-+------------------------------
   created       | 2025-09-12 13:26:42.994842+03
   deleted       | 2025-09-12 13:37:22.889837+03
   last_ddl_time | 2025-09-12 13:30:35.061483+03

7. Очистите запись об удаленной таблице:

   SELECT purge_object_mod_dates();

   Пример успешного выполнения запроса:

   -[ RECORD 1 ]----------+-
   purge_object_mod_dates |

8. Проверьте, что запись о таблице отсутствует в представлении psql_dba_objects:

   SELECT created, deleted, last_ddl_time FROM psql_dba_objects
   WHERE name = 't1' AND
        type = 'table' AND
        ispredef = 'f';

   Пример успешного выполнения запроса:

    created | deleted | last_ddl_time
   ---------+---------+---------------
   (0 rows)