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

Представьте себе мир, в котором каждое изменение вашей базы данных оставляет след, фиксирующую эволюцию ваших данных. В этом заключаются перспективы исторических таблиц — концепции, которая выходит за рамки традиционных ограничений проектирования баз данных. В этом исследовании мы углубимся во временное измерение PostgreSQL, раскроем возможности Postgres-триггеров и их ключевую роль в создании и обслуживании исторических таблиц.

Что такое историческая таблица?

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

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

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

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

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

Пример триггеров

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

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

CREATE OR REPLACE FUNCTION tp_history_func() RETURNS TRIGGER AS
$$
DECLARE
    tbl_history TEXT        := FORMAT('%I.%I', TG_TABLE_SCHEMA, TG_TABLE_NAME || '_history');
    next_id     BIGINT      := NEXTVAL(TG_TABLE_SCHEMA || '.' || TG_TABLE_NAME || '_history_seq');
    curr_time   TIMESTAMPTZ := NOW();
    deleted_by  TEXT        := NULL;
BEGIN
    IF (TG_OP = 'DELETE') THEN
        deleted_by = current_setting('history.deleted_by', true);
        EXECUTE 'INSERT INTO ' || tbl_history || ' SELECT $1, $2, $3, $4, $5.*'
            USING next_id, curr_time, deleted_by, TG_OP, OLD;
        RETURN OLD;
    ELSIF (TG_OP = 'UPDATE') THEN
        EXECUTE 'INSERT INTO ' || tbl_history ||
                ' SELECT $1, $2, $3, $4, $5.*' USING next_id, curr_time, deleted_by, TG_OP, NEW;
        RETURN NEW;
    ELSIF (TG_OP = 'INSERT') THEN
        EXECUTE 'INSERT INTO ' || tbl_history ||
                ' SELECT $1, $2, $3, $4, $5.*' USING next_id, curr_time, deleted_by, TG_OP, NEW;
        RETURN NEW;
    END IF;
    RETURN NULL;
    -- Foreign key violation means required related entity doesn't exist anymore.
    -- Just skipping trigger invocation
EXCEPTION
    WHEN foreign_key_violation THEN
        RETURN NULL;
END;
$$
    LANGUAGE plpgsql;

Давайте посмотрим, как работает триггер. Прежде всего, триггерная функция является универсальной и обрабатывает любую таблицу, в которую она добавляется. ‘next_id’ вычисляет идентификатор следующей ревизии, используя NEXTVAL.

Затем у нас есть вариант IF-ELSE для обработки операций INSERT, UPDATE и DELETE независимо. NEW представляет новую строку базы данных для операций INSERT/UPDATE в триггерах для строки; эта переменная имеет значение null в триггерах для операций DELETE. OLD представляет старую строку базы данных для операций UPDATE/DELETE в триггерах уровня строки; эта переменная имеет значение null в триггерах для операции INSERT. TG_OP хранит текстовое название операции, для которой был запущен триггер: INSERT, UPDATE, DELETE или TRUNCATE.

Хорошо, теперь давайте проверим, как мы можем его использовать:

CREATE TABLE IF NOT EXISTS users
(
    id              UUID PRIMARY KEY            DEFAULT gen_random_uuid(),
    first_name      VARCHAR                     NOT NULL,
    last_name       VARCHAR,
    email           VARCHAR,
    phone           VARCHAR,
    created_by      VARCHAR,
    created_at      TIMESTAMPTZ                  DEFAULT NOW(),
    modified_by     VARCHAR,
    modified_at     TIMESTAMPTZ                  DEFAULT NOW(),
);

CREATE SEQUENCE IF NOT EXISTS users_history_seq
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 9223372036854775807
    START 1
    CACHE 1
    NO CYCLE;

CREATE TRIGGER trg_users
    AFTER INSERT OR UPDATE OR DELETE
    ON users
    FOR EACH ROW
EXECUTE PROCEDURE tp_history_func();

CREATE TABLE IF NOT EXISTS users_history
(
    history_id         BIGSERIAL PRIMARY KEY,
    history_timestamp  TIMESTAMPTZ NOT NULL DEFAULT now(),
    history_deleted_by VARCHAR,
    history_op         VARCHAR     NOT NULL,
    id                 UUID,
    first_name         VARCHAR,
    last_name          VARCHAR,
    email              VARCHAR,
    phone              VARCHAR,
    created_by         VARCHAR,
    created_at         TIMESTAMPTZ,
    modified_by        VARCHAR,
    modified_at        TIMESTAMPTZ
);

DROP INDEX IF EXISTS idx_users_history_ids;
CREATE INDEX IF NOT EXISTS idx_users_history_ids ON users_history (history_id, id);

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

Нам также следует добавить триггер, использующий tp_history_func.

Ниже я приложил несколько скринов, чтобы проиллюстрировать процесс добавления нового пользователя в базу данных. Триггер плавно инициирует добавление новой записи в таблицу user_history.

Заключение

Применение всего 20 строк кода позволяет расширить традиционные границы управления базами данных, предоставляя плавный мост между прошлым, настоящим и будущим. Триггеры Postgres предоставляет возможность перемещаться по истории баз данных, создавая перспективы для точного анализа и принятия обоснованных решений.

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