Настройка триггера событий PostgreSQL

Введение

В этой лабораторной работе вы научитесь настраивать триггер событий в PostgreSQL. Триггеры событий перехватывают события языка определения данных (DDL), такие как создание, изменение или удаление объектов базы данных.

Вы создадите специальную таблицу журналов и особую функцию для записи этих событий. Затем вы создадите триггер событий, который срабатывает после команды CREATE TABLE, регистрируя детали новой таблицы. Это практическое упражнение проведет вас через создание необходимых компонентов, тестирование функциональности триггера и последующее удаление объектов базы данных.

Создание таблицы журналов и функции триггера

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

Сначала откройте терминал и подключитесь к базе данных PostgreSQL с помощью интерактивной оболочки psql. Последующие операции SQL вы будете выполнять в этой оболочке.

sudo -u postgres psql

Теперь вы должны увидеть приглашение PostgreSQL, которое выглядит как postgres=#.

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

Выполните следующую команду SQL в оболочке psql:

CREATE TABLE event_log (
    event_time timestamp with time zone,
    event_type text,
    schema_name text,
    object_name text,
    command_tag text
);

После успешного выполнения PostgreSQL ответит:

CREATE TABLE

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

CREATE OR REPLACE FUNCTION log_ddl_event()
RETURNS event_trigger
AS $$
BEGIN
    INSERT INTO event_log (event_time, event_type, schema_name, object_name, command_tag)
    SELECT now(), tg_event, ddl_command.schema_name, ddl_command.objid::regclass, tg_tag
    FROM pg_event_trigger_ddl_commands() AS ddl_command;
END;
$$ LANGUAGE plpgsql;

Разберем эту функцию:

• RETURNS event_trigger: Указывает, что это специальный тип функции, предназначенный для использования с триггерами событий.
• AS $$ ... $$ LANGUAGE plpgsql: Определяет тело функции с использованием процедурного языка plpgsql.
• INSERT INTO event_log ...: Это основное действие, которое вставляет новую строку в нашу таблицу журналов.
• now(): Стандартная функция, возвращающая текущую временную метку.
• tg_event, tg_tag: Специальные переменные, доступные внутри триггерной функции, которые предоставляют контекст о событии, например ddl_command_end и CREATE TABLE.
• pg_event_trigger_ddl_commands(): Функция, которая возвращает набор строк, описывающих выполненные команды DDL. Мы используем ее для получения schema_name и objid (идентификатор объекта) создаваемого объекта.

После выполнения команды вы увидите следующее подтверждение:

CREATE FUNCTION

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

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

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

В той же оболочке psql выполните следующую команду, чтобы создать триггер событий с именем log_table_creation:

CREATE EVENT TRIGGER log_table_creation
ON ddl_command_end
WHEN TAG IN ('CREATE TABLE')
EXECUTE FUNCTION log_ddl_event();

Давайте рассмотрим компоненты этого оператора:

• CREATE EVENT TRIGGER log_table_creation: Это имя вашего нового триггера событий.
• ON ddl_command_end: Это указывает время срабатывания триггера. Он сработает после завершения выполнения команды DDL.
• WHEN TAG IN ('CREATE TABLE'): Это условие фильтрации. Триггер будет активирован только в том случае, если тег команды соответствует CREATE TABLE. Вы также можете добавить сюда другие теги, такие как ALTER TABLE или DROP TABLE.
• EXECUTE FUNCTION log_ddl_event(): Это указывает, какую функцию следует выполнить при выполнении условий триггера.

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

CREATE EVENT TRIGGER

Ваш триггер событий теперь активен и будет регистрировать каждое создание новой таблицы.

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

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

Сначала создайте простую таблицу с именем employees:

CREATE TABLE employees (
    id serial PRIMARY KEY,
    name text NOT NULL
);

Команда CREATE TABLE выполнится как обычно, и вы увидите стандартное подтверждение:

CREATE TABLE

За кулисами это действие должно было вызвать ваш триггер log_table_creation. Чтобы проверить это, выполните запрос к таблице event_log, чтобы увидеть ее содержимое:

SELECT schema_name, object_name, command_tag FROM event_log;

Вывод покажет несколько записей. Это связано с тем, что создание таблицы с serial PRIMARY KEY также неявно создает последовательность (sequence) и ограничение первичного ключа (primary key constraint). Триггер событий фиксирует все связанные команды DDL, что приводит к нескольким записям в журнале.

 schema_name |   object_name    | command_tag
-------------+------------------+--------------
 public      | employees_id_seq | CREATE TABLE
 public      | employees        | CREATE TABLE
 public      | employees_pkey   | CREATE TABLE
 public      | employees_id_seq | CREATE TABLE
(4 rows)

Этот результат подтверждает, что ваш триггер событий функционирует правильно и регистрирует создание таблицы вместе с зависимыми от нее объектами.

Очистка объектов базы данных

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

Сначала удалите триггер событий:

DROP EVENT TRIGGER log_table_creation;

Вы увидите подтверждающее сообщение:

DROP EVENT TRIGGER

Далее удалите две созданные вами таблицы: employees и event_log:

DROP TABLE employees;
DROP TABLE event_log;

PostgreSQL подтвердит каждое действие:

DROP TABLE
DROP TABLE

Наконец, удалите функцию триггера:

DROP FUNCTION log_ddl_event();

Вы увидите финальное подтверждение:

DROP FUNCTION

Теперь вы успешно очистили все объекты, созданные в этой лабораторной работе. Чтобы выйти из оболочки psql и вернуться к обычному приглашению терминала, введите \q и нажмите Enter.

\q

Резюме

В этой лабораторной работе вы научились реализовывать триггеры событий в PostgreSQL для мониторинга действий DDL. Вы создали таблицу журналов для хранения данных событий и функцию на PL/pgSQL для их заполнения. Затем вы определили триггер событий, который специально отслеживает события CREATE TABLE, связав его с вашей функцией журналирования. Протестировав триггер и проверив запись в журнале, вы получили практический опыт совместной работы этих компонентов. Наконец, вы применили лучшие практики управления базами данных, очистив созданные триггер, таблицы и функцию.