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