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