Использование Postgres-триггеров для исторических таблиц

В динамичной среде управления базами данных постоянно присутствует необходимость фиксировать и понимать изменения данных с течением времени. Начните управлять временем с использованием 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-триггеров вы осуществляете эффективное управление, обеспечивая координацию исторических событий в базе данных. Каждое триггерное событие создает гармоничное сочетание прошлого, настоящего и будущего в вашей базе данных, оставляя след во времени.