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

Введение

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

Вы создадите функцию триггера событий с именем log_ddl_event, которая вставляет детали события в таблицу event_log. Затем вы настроите триггер событий DDL (DDL event trigger), чтобы выполнять эту функцию всякий раз, когда создается новая таблица. Наконец, вы протестируете триггер, создав новую таблицу и убедившись, что событие зарегистрировано правильно, а затем удалите триггер событий.

Skills Graph

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

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

Сначала подключитесь к базе данных PostgreSQL от имени пользователя postgres:

sudo -u postgres psql

Теперь создайте таблицу event_log, выполнив следующую SQL-команду:

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

Эта команда создает таблицу с именем event_log со столбцами для хранения времени события, типа события, имени схемы, имени объекта и тега команды (command tag).

Далее создайте функцию триггера событий log_ddl_event. Выполните следующую SQL-команду:

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)
    VALUES (NOW(), TG_EVENT, TG_TABLE_SCHEMA, TG_TABLE_NAME, TG_TAG);
END;
$$ LANGUAGE plpgsql;

Эта команда создает функцию с именем log_ddl_event. Давайте разберем, что делает эта функция:

• CREATE OR REPLACE FUNCTION log_ddl_event(): Создает или заменяет функцию с именем log_ddl_event.
• RETURNS event_trigger: Указывает, что функция является функцией триггера событий (event trigger function).
• AS $$ ... $$ LANGUAGE plpgsql: Определяет тело функции с использованием языка plpgsql.
• INSERT INTO event_log ...: Вставляет новую строку в таблицу event_log с деталями события.
• NOW(): Получает текущую метку времени (timestamp).
• TG_EVENT: Специальная переменная, содержащая тип события, которое вызвало функцию (например, CREATE TABLE).
• TG_TABLE_SCHEMA: Специальная переменная, содержащая имя схемы объекта, который вызвал функцию.
• TG_TABLE_NAME: Специальная переменная, содержащая имя объекта, который вызвал функцию.
• TG_TAG: Специальная переменная, содержащая тег команды (command tag) события (например, CREATE TABLE).

После выполнения вышеуказанных команд вы должны увидеть CREATE TABLE и CREATE FUNCTION в выводе, что указывает на успешное создание таблицы и функции.

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

На этом шаге вы создадите триггер событий DDL (DDL event trigger), который выполняет функцию log_ddl_event всякий раз, когда создается новая таблица.

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

CREATE EVENT TRIGGER log_table_creation
ON ddl_command_end
WHEN TAG IS 'CREATE TABLE'
EXECUTE PROCEDURE log_ddl_event();

Эта команда создает триггер событий с именем log_table_creation. Давайте разберем, что делает этот триггер:

• CREATE EVENT TRIGGER log_table_creation: Создает триггер событий с именем log_table_creation.
• ON ddl_command_end: Указывает, что триггер будет срабатывать в конце команды DDL.
• WHEN TAG IS 'CREATE TABLE': Указывает, что триггер будет срабатывать только тогда, когда тег команды (command tag) равен CREATE TABLE.
• EXECUTE PROCEDURE log_ddl_event(): Указывает, что функция log_ddl_event должна быть выполнена при срабатывании триггера.

После выполнения вышеуказанной команды вы должны увидеть CREATE EVENT TRIGGER в выводе.

Тестирование триггера

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

Создайте новую таблицу с именем test_table, выполнив следующую SQL-команду:

CREATE TABLE test_table (
    id serial PRIMARY KEY,
    name text
);

Эта команда создает таблицу с именем test_table со столбцом id и столбцом name. Вы должны увидеть CREATE TABLE в выводе.

Далее убедитесь, что триггер событий зарегистрировал событие создания таблицы в таблице event_log. Выполните следующую SQL-команду:

SELECT * FROM event_log;

Эта команда выбирает все строки из таблицы event_log. Вы должны увидеть строку с деталями события CREATE TABLE для test_table. Вывод должен выглядеть примерно так:

       event_time        | event_type  | schema_name | object_name | command_tag
-----------------------+-------------+-------------+-------------+-------------
 2023-10-27 10:00:00+00 | ddl_command_end | public      | test_table  | CREATE TABLE
(1 row)

event_time будет временем, когда вы создали таблицу. event_type будет ddl_command_end. schema_name будет public (или схемой, в которой вы создали таблицу). object_name будет test_table. command_tag будет CREATE TABLE.

Удаление триггера и таблиц

На этом шаге вы удалите триггер событий и созданные вами таблицы.

Сначала удалите триггер событий log_table_creation, выполнив следующую SQL-команду:

DROP EVENT TRIGGER log_table_creation;

Вы должны увидеть DROP EVENT TRIGGER в выводе.

Затем удалите таблицу test_table и таблицу event_log, выполнив следующие SQL-команды:

DROP TABLE test_table;
DROP TABLE event_log;

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

DROP FUNCTION log_ddl_event();

На этом лабораторная работа завершена. Вы успешно создали функцию триггера событий, настроили триггер событий DDL (DDL event trigger), протестировали триггер и удалили триггер.

Выйдите из оболочки psql, набрав \q и нажав Enter.

Итог

В этой лабораторной работе вы узнали, как создать триггер событий (event trigger) в PostgreSQL. Вы начали с создания функции триггера событий, log_ddl_event, которая регистрирует детали событий базы данных в таблице event_log. Затем вы настроили триггер событий DDL (DDL event trigger) для выполнения этой функции всякий раз, когда создается новая таблица. Наконец, вы протестировали триггер, создав новую таблицу и убедившись, что событие было зарегистрировано правильно, а затем удалили триггер событий.