В продолжение сегодняшнего топика Версионность и история данных — поделюсь простеньким инструментом, который мы используем.Dklab_rowlog — это библиотека из нескольких хранимых процедур PostgreSQL, позволяющая добавить версионирование записей к любой таблице в БД. Иными словами, что бы ни происходило с таблицей, как бы данные там ни менялись (добавлялись/удалялись), это будет отражено в специальной табличке-логе.
Преимущества:
- Версионирование добавляется к любой таблице за 1 минуту с использованием 1 SQL-команды.
- Можно указать, какие колонки надо сохранять, а какие — нет (что экономит место). При этом запись в лог добавится только в случае, если хотя бы одна из указанных колонок изменилась.
- Можно указать колонку, которая будет трактоваться как «ID автора изменения».
- Можно задать колонки, которые будут логироваться в любом случае, независимо от того, менялись они или нет.
Примеры использования
Предположим, нам нужно логировать изменения вот в такой табличке:
CREATE TABLE test_src1 (
id bigint NOT NULL,
a character varying(20),
b character varying(20),
c character varying(20),
modified_by bigint NOT NULL
);
Пример 1: отслеживаем изменения только в колонках "a" и "c". Как только одно из этих полей изменилось, в public.rowlog будет добавлена запись об этом.
CREATE TRIGGER t_rowlog
AFTER INSERT OR DELETE OR UPDATE ON test_src1 FOR EACH ROW
EXECUTE PROCEDURE rowlog.t_rowlog_aiud('diff=>a', 'diff=>c', 'rowlog=>public.rowlog');
Пример 2: всегда добавлять запись в rowlog при изменении строки в таблице, но сохранять только колонки "a" и "b". Можно, кстати, не задавать параметр 'rowlog=>xxx', т.к. по умолчанию он равен CURRENT_SCHEMA.rowlog.
CREATE TRIGGER t_rowlog
AFTER INSERT OR DELETE OR UPDATE ON test_src1 FOR EACH ROW
EXECUTE PROCEDURE rowlog.t_rowlog_aiud('always=>a', 'always=>b');
Пример 3: в каждой записи в логе сохраняем ID "автора изменения". Также можно в явном виде указать, как называется первичный ключ таблицы (по умолчанию — "id").
CREATE TRIGGER t_rowlog
AFTER INSERT OR DELETE OR UPDATE ON test_src1 FOR EACH ROW
EXECUTE PROCEDURE rowlog.t_rowlog_aiud('always=>a', 'author=>modified_by', 'pk=>id');
Структура таблицы-лога
Структура примерно следующая:
CREATE TABLE rowlog (
-- Row version primary key.
id BIGSERIAL NOT NULL,
-- Timestamp of this version creation.
stamp timestamp WITH time zone DEFAULT now() NOT NULL,
-- Who modified a source row? You may specify any type, not only BIGINT.
author bigint,
-- Table OID of the changed row.
rel regclass NOT NULL,
-- Previous row columns.
data_old hstore.hstore NOT NULL,
-- Resulting row columns.
data_new hstore.hstore NOT NULL,
-- Change operation (INSERT/UPDATE/DELETE).
operation enum_tg_op NOT NULL,
-- Primary key of the source table's row.
pk bigint,
CONSTRAINT "rowlog_pkey" PRIMARY KEY("id")
);
Можно добавлять и другие поля, навешивать индексы и т.д. Можно в одной и той же таблице-логе хранить записи из разных таблиц (чаще всего это оказывается удобно, потому что в таком случае добавление версионности сводится к единственной команде CREATE TRIGGER).
Ограничения
Нужно учитывать 2 вещи при использовании:
- Библиотека не рассчитана на ультравысокие нагрузки, т.к. там внутри несколько EXECUTE SQL-ей. Но несколько тысяч инсертов в секунду она легко выдерживает.
- Хранение измененных данных в hstore удобно тем, что новые поля в таблицу-источник можно быстро добавлять, однако есть и недостаток: если таблица-источник структурно сильно меняется во времени (например, там поля удаляются или переименовываются), в hstore останутся старые версии со старой структурой.
