Как реализовать журнал DDL операций в PostgreSQL? Пример создания DDL Log

Приветствую Вас на сайте Info-Comp.ru! Сегодня мы с Вами рассмотрим пример создания DDL лога в PostgreSQL – это своего рода журнал DDL операций и реализовывать его мы будем с помощью триггеров событий.

Содержание

Что такое DDL лог и зачем он нужен
Как создать DDL Log в PostgreSQL
Триггеры событий в PostgreSQL
Типы событий
Команды DDL, на которые срабатывают триггеры событий
Функции для триггеров событий
Функции для получения информации в событийных триггерах
Команда CREATE EVENT TRIGGER
Пример создания DDL лога в PostgreSQL
Таблица для хранения информации
Функция, которая будет вызываться при наступлении события
Создание событийных триггеров
Пример работы DDL лога

Что такое DDL лог и зачем он нужен

DDL лог – это журнал выполненных DDL операций в базе данных. Проще говоря – это таблица, в которой отражены все DDL инструкции, выполненные в базе данных. В этой таблице может храниться информация о том, кто, когда, с какими объектами и какие именно действия выполнял в базе данных.

Иными словами, если Вы выполните в базе данных инструкцию CREATE TABLE, CREATE FUNCTION или любую другую команду, относящуюся к DDL, то в этой таблице будет сохранена информация о том, что Вы в такое-то время выполнили такое-то действие.

Заметка! Что такое DDL, DML, DCL и TCL в языке SQL.

Таким образом, администраторы или разработчики всегда будут знать о том, кто и когда создавал, изменял или удалял объекты базы данных.

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

Как создать DDL Log в PostgreSQL

На сегодняшний день одним из самых распространённых способов создания DDL лога, как минимум в Microsoft SQL Server и PostgreSQL, является способ с использованием триггеров.

В частности, в PostgreSQL для подобных задач существуют так называемые триггеры событий (Event Triggers).

Однако данная возможность, т.е. данный тип триггеров, появился не сразу, а только начиная с версии 9.3. До этой версии использовались другие, менее удобные способы реализации DDL лога, их сегодня мы не будем рассматривать, рассматривать мы будем способ с использованием триггеров событий.

Триггеры событий в PostgreSQL

Триггер – это действия, оформленные в виде функции, которые выполняются в результате наступления определенных событий.

Если говорить точнее, в PostgreSQL триггер – это указание, что необходимо выполнить заданную функцию при наступлении определённого события. Иными словами, в PostgreSQL триггер не хранит в себе код тех действий, которые необходимо выполнить, как, например, в Microsoft SQL Server, здесь он всего лишь связывает событие с функцией.

Триггеры событий (Event Triggers) в отличие от обычных триггеров, которые срабатывают на команды DML, работают с командами DDL, иными словами – это DDL триггеры.

Заметка! Как подключиться к PostgreSQL с помощью Azure Data Studio.

Типы событий

На текущий момент в PostgreSQL существуют 4 события, на которые может срабатывать данный тип триггеров:

ddl_command_start – событие происходит перед выполнением DDL команды. Стоит отметить, что проверка на существование объекта перед срабатыванием триггера не производится. Данное событие можно использовать тогда, когда Вы хотите запретить выполнение определенных команд в базе данных;
ddl_command_end – событие происходит после выполнения DDL команды. Это событие можно использовать как раз для реализации нашей задачи, т.е. создания DDL лога;
sql_drop – событие происходит перед событием ddl_command_end для команд, которые удаляют объекты базы данных. Дело в том, что в событии ddl_command_end мы не можем получить информацию о том, какие именно объекты были удалены, поэтому существует отдельное событие на команды уделения объектов (DROP);
table_rewrite – событие происходит после того, как таблица будет перезаписана в результате определённых действий команд ALTER TABLE и ALTER TYPE. Данное событие используется для каких-то специфичных целей, на текущий момент мне оно не требовалось.

Для создания DDL лога мы будем использовать два события: ddl_command_end для отслеживания и получения информации о создаваемых и изменяемых объектах базы данных, и sql_drop для получения информации об удаляемых объектах базы данных в PostgreSQL.

Примечание! Если есть несколько триггеров на одно и то же событие, то они будут срабатывать в алфавитном порядке по имени триггера.

Команды DDL, на которые срабатывают триггеры событий

На самом деле операций в PostgreSQL, на которые срабатывают триггеры событий, много, т.е. по сути всё, что относится к DDL, поэтому все перечислять их здесь мы, конечно же, не будем, так как полный перечень команд можно посмотреть в документации – https://postgrespro.ru/docs/postgresql/16/event-trigger-matrix.

Единственное, отмечу, что все самые популярные и распространённые операции, безусловно, поддерживаются, такие как:

CREATE TABLE
ALTER TABLE
DROP TABLE
CREATE FUNCTION
DROP FUNCTION
CREATE VIEW
ALTER VIEW
DROP VIEW
И все остальные DDL операции

Функции для триггеров событий

В PostgreSQL для того, чтобы создать триггер событий, необходимо сначала создать отдельную специальную функцию, в которой описываются все действия, которые мы хотим, чтобы выполнялись в момент наступления того или иного события.

В нашем случае в данной функции мы будем просто получать информацию об изменённых объектах и сохранять ее в предварительно созданную нами таблицу DDL лога.

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

Также стоит отметить, что писать код в данных функциях необходимо на процедурных языках, таких как PL/pgSQL или любом другом поддерживаемом языке, однако написать код на чистом SQL не получится.

Заметка! Как создать составной тип данных с помощью pgAdmin 4 в PostgreSQL.

Функции для получения информации в событийных триггерах

Чтобы получить информацию о событии и объектах, которые затрагиваются в результате выполнения DDL операции, в PostgreSQL существует несколько системных функций и переменных, которые мы можем использовать в своих, создаваемых для триггеров событий, функциях.

Переменные:

TG_event – имя события, при котором срабатывает этот триггер;
TG_tag – имя команды, для которой срабатывает этот триггер.

Функции:

pg_event_trigger_ddl_commands () – возвращает информацию о DDL командах, которые спровоцировали срабатывание триггера, а также о затрагиваемых объектах. На одно событие функция может возвращать несколько записей, дело в том, что некоторые DDL команды могут скрывать под собой на самом деле несколько операций, например, в инструкции CREATE TABLE могут быть определены столбцы IDENTITY, для которых будет создаваться последовательность, если у столбцов определено ограничение DEFAULT или PRIMARY KEY, то оно также будет создаваться отдельно, для текстовых столбцов будет создаваться отдельная Toast таблица и так далее. Вызывать ее можно только в функциях, которые определены для события ddl_command_end;
pg_event_trigger_dropped_objects () – возвращает список всех объектов, которые были удалены в результате DDL команды. Она также может возвращать набор данных на одно событие. Вызывать ее можно только в функциях, которые определены для события sql_drop;
pg_event_trigger_table_rewrite_oid () – возвращает OID таблицы, которая будет перезаписана;
pg_event_trigger_table_rewrite_reason () – возвращает код причины, вызвавшей перезапись. Конкретные значения кодов зависят от версии сервера.

Примечание! Посмотреть, какие данные возвращают эти функции, можно в документации https://postgrespro.ru/docs/postgresql/16/functions-event-triggers

Команда CREATE EVENT TRIGGER

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

Создается триггер событий командой CREATE EVENT TRIGGER.

Синтаксис

CREATE EVENT TRIGGER Имя триггера ON Событие

[WHEN Переменная фильтра IN (Значение фильтра)]

EXECUTE FUNCTION имя функции ()

Где,

Имя триггера – имя нового триггера, оно должно быть уникальным в рамках базы данных;
Событие – имя события, при котором срабатывает триггер;
Переменная фильтра – имя переменной, применяемой для фильтрации событий. Это позволяет ограничить срабатывание триггера, например, мы хотим, чтобы триггер срабатывал не на все DDL команды, а только на некоторые. На текущий момент поддерживается только переменная TAG;
Значение фильтра – список значений, связанный с «Переменной фильтра», для которых должен срабатывать триггер. Например, мы хотим отслеживать только создание таблиц, для этого в качестве значения мы можем указать «(‘CREATE TABLE’,’SELECT INTO’,’CREATE TABLE AS’)». Полный список тегов можно посмотреть в документации;
Имя функции – пользовательская функция, которая будет вызываться при срабатывании триггера. Как уже отмечалось, она должны быть без параметров и возвращать тип event_trigger.

Заметка! Как в PostgreSQL создать функцию, возвращающую табличные данные.

Пример создания DDL лога в PostgreSQL

Ну а теперь давайте рассмотрим пример реализации DDL лога в PostgreSQL.

Таблица для хранения информации

Сначала создаём таблицу для хранения информации, которая нам необходима, иными словами, где и будет располагаться сам DDL лог.

  
  CREATE TABLE IF NOT EXISTS DDL_Log
  (
    "LogId"               Int           NOT NULL GENERATED ALWAYS AS IDENTITY,
    "LogDate"             TimeStamp     NOT NULL,
    "ClassId"             Int               NULL,
    "ObjId"               Int               NULL,
    "ObjsubId"            Int               NULL,
    "ClientAddr"          Inet              NULL,
    "UserName"            VarChar(256)      NULL,
    "EventType"           VarChar(100)      NULL,
    "ObjectType"          Text              NULL,
    "SchemaName"          Text              NULL,
    "ObjectName"          Text              NULL,
    "Command"             Text              NULL,
    "CommandTag"          Text              NULL,
    "CommandText"         Text              NULL,
    CONSTRAINT "PK_DDL_Log_LogId" PRIMARY KEY ("LogId")
  );

Заметка! Как создать таблицу в PostgreSQL с помощью pgAdmin 4.

Функция, которая будет вызываться при наступлении события

Затем мы создаем функцию, в которой мы описываем все, что мы хотим сделать в момент срабатывания триггера, в нашем случае это будет просто считывание данных и INSERT в таблицу.

  
  CREATE OR REPLACE FUNCTION DDL_Log_Record_Add()
    RETURNS event_trigger
    LANGUAGE plpgsql
  AS 
  $BODY$
  DECLARE
    _command_text  Text          = current_query();    -- Текст SQL инструкции
    _date          TimeStamp     = now();              -- Текущая дата
    _client_addr   Inet          = inet_client_addr(); -- IP адрес клиента, с которого запущена SQL инструкция
    _user_name     VarChar(256)  = current_user;       -- Имя пользователя, от которого запущена SQL инструкция
    _tg_event      Text          = TG_event;           -- Имя события
    _tg_tag        Text          = TG_tag;             -- SQL команда

  BEGIN

    IF _tg_event  = 'sql_drop'
    THEN

      INSERT INTO DDL_Log
      (
         "LogDate",
         "ClassId",
         "ObjId",
         "ObjsubId",
         "ClientAddr",
         "UserName",
         "EventType",
         "ObjectType",
         "SchemaName",
         "ObjectName",
         "Command",
         "CommandTag",
         "CommandText"
      )
      SELECT
        _date,
        D.classid,
        D.objid,
        D.objsubid,
        _client_addr,
        _user_name,
        _tg_event,
        D.object_type,
        D.schema_name,
        D.object_identity,
        _tg_tag,
        NULL,
        _command_text
      FROM pg_event_trigger_dropped_objects() D
      WHERE D.schema_name NOT IN
       (
         'pg_temp',
         'pg_toast'
       );

    ELSE

      INSERT INTO DDL_Log
      (
         "LogDate",
         "ClassId",
         "ObjId",
         "ObjsubId",
         "ClientAddr",
         "UserName",
         "EventType",
         "ObjectType",
         "SchemaName",
         "ObjectName",
         "Command",
         "CommandTag",
         "CommandText"
      )
      SELECT
        _date,
        D.classid,
        D.objid,
        D.objsubid,
        _client_addr,
        _user_name,
        _tg_event,
        D.object_type,
        D.schema_name,
        D.object_identity,
        _tg_tag,
        D.command_tag,
        _command_text
      FROM pg_event_trigger_ddl_commands() D
      WHERE D.schema_name NOT IN
       (
         'pg_temp',
         'pg_toast'
       );

    END IF;

  END;

  $BODY$;

В данном случае у нас будет одна функция, которую, как было уже отмечено ранее, мы будем привязывать к двум событиям: ddl_command_end для отслеживания создаваемых и изменяемых объектов базы данных, и sql_drop для отслеживания удаляемых объектов.

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

При этом также стоит отметить, что отслеживаются все объекты, даже временные, однако если Вам не нужно отслеживать эти объекты, то Вы можете просто их отфильтровывать.

Например, в нашем случае я не сохраняю информацию о временных объектах и о TOAST таблицах, которые, например, создаются для текстовых столбцов.

Кроме информации из системных событийных функций, в данном случае мы сохраняем еще и дополнительную информацию, которая может быть нам полезна, например, текст SQL инструкции, имя пользователя, IP адрес клиента, и, конечно же, дату выполнения текущих действий.

Заметка! Нормализация баз данных простыми словами.

Создание событийных триггеров

Таблицу и функцию мы создали, теперь осталось создать триггер события, который и будет связывать нашу функцию с событием.

Как было уже отмечено, мы создаем два триггера на разные события, в частности: ddl_command_end и sql_drop.

  
  CREATE EVENT TRIGGER DDL_Log_CREATE ON ddl_command_end
    EXECUTE FUNCTION DDL_Log_Record_Add();

  CREATE EVENT TRIGGER DDL_Log_DROP ON sql_drop
    EXECUTE FUNCTION DDL_Log_Record_Add();

На этом все, DDL лог готов, осталось только проверить его работу.

Пример работы DDL лога

Чтобы проверить, сохраняются ли данные об операциях, давайте выполним несколько DDL команд.

Создание таблицы

  
  CREATE TABLE IF NOT EXISTS TestTable
  (
    "Id"               Int           NOT NULL GENERATED ALWAYS AS IDENTITY,
    "Date"             TimeStamp     NOT NULL DEFAULT Now(),
    "Number"           Int               NULL,
    "Name"             Text              NULL,
    CONSTRAINT "PK_TestTable_Id" PRIMARY KEY ("Id")
  );

  SELECT * FROM DDL_Log;

Удаление таблицы

  
  DROP TABLE IF EXISTS TestTable;

  SELECT * FROM DDL_Log;

Как видим, данные сохраняются в логе.

Заметка! Всем тем, кто только начинает свое знакомство с языком SQL, рекомендую прочитать книгу «SQL код» – это самоучитель по языку SQL, в нем очень подробно рассмотрены основные конструкции языка.

На сегодня это все, надеюсь материал был Вам полезен, пока!