В продолжение сегодняшнего топика Версионность и история данных — поделюсь простеньким инструментом, который мы используем.

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 вещи при использовании:

1. Библиотека не рассчитана на ультравысокие нагрузки, т.к. там внутри несколько EXECUTE SQL-ей. Но несколько тысяч инсертов в секунду она легко выдерживает.

2. Хранение измененных данных в hstore удобно тем, что новые поля в таблицу-источник можно быстро добавлять, однако есть и недостаток: если таблица-источник структурно сильно меняется во времени (например, там поля удаляются или переименовываются), в hstore останутся старые версии со старой структурой.

Поэтому на практике я бы порекомендовал применять библиотеку в основном для целей подсчета различной статистики по живой базе или ее реплике (KPI).