Автор оригинала: Vlad Mihalcea.
Вступление
В этой статье мы рассмотрим, как мы можем реализовать механизм ведения журнала аудита с использованием триггеров базы данных PostgreSQL для хранения записей CDC (Сбора данных об изменениях).
Благодаря типам столбцов JSON мы можем хранить состояние строки в одном столбце, поэтому нам не нужно добавлять новый столбец в таблицу журнала аудита каждый раз, когда новый столбец добавляется в исходную таблицу базы данных.
Таблицы базы данных
Давайте предположим, что мы разрабатываем библиотечное приложение, которое использует следующие две таблицы:
В таблице книга
мы будем хранить все книги, предлагаемые нашей библиотекой, а в таблице book_audit_log
будут храниться события CDC (Сбор данных об изменении) , которые происходили всякий раз, когда в таблице книга
выполнялась инструкция INSERT, UPDATE или DELETE DML.
Таблица book_audit_log
создается следующим образом:
CREATE TABLE IF NOT EXISTS book_audit_log ( book_id bigint NOT NULL, old_row_data jsonb, new_row_data jsonb, dml_type dml_type NOT NULL, dml_timestamp timestamp NOT NULL, dml_created_by varchar(255) NOT NULL, PRIMARY KEY (book_id, dml_type, dml_timestamp) )
В столбце book_id
хранится идентификатор связанной записи book
таблицы, которая была вставлена, обновлена или удалена текущей выполняемой инструкцией DML.
old_row_data
– это столбец JSONB, который фиксирует состояние строки book
перед выполнением текущей инструкции INSERT, UPDATE или DELETE.
new_row_data
– это столбец JSONB, который будет фиксировать состояние строки book
после выполнения текущей инструкции INSERT, UPDATE или DELETE.
В столбце dml_type
хранится тип текущего выполняемого оператора DML (например, ВСТАВКА, ОБНОВЛЕНИЕ и УДАЛЕНИЕ). Тип dml_type
– это тип перечисления PostgreSQL, который был создан следующим образом:
CREATE TYPE dml_type AS ENUM ('INSERT', 'UPDATE', 'DELETE')
В столбце dml_timestamp
хранится текущая метка времени.
В столбце dml_created_by
хранится пользователь приложения, создавший текущую инструкцию INSERT, UPDATE или DELETE DML.
Первичный ключ book_audit_log
является составным из book_id
, dmi_type
и dmi_timestamp
, поскольку запись book
может иметь несколько связанных book_audit_log
записей.
Триггеры ведения журнала аудита PostgreSQL
Чтобы записать инструкции INSERT, UPDATE и DELETE DML в таблице book
, нам нужно создать функцию триггера, которая выглядит следующим образом:
CREATE OR REPLACE FUNCTION book_audit_trigger_func() RETURNS trigger AS $body$ BEGIN if (TG_OP = 'INSERT') then INSERT INTO book_audit_log ( book_id, old_row_data, new_row_data, dml_type, dml_timestamp, dml_created_by ) VALUES( NEW.id, null, to_jsonb(NEW), 'INSERT', CURRENT_TIMESTAMP, current_setting('var.logged_user') ); RETURN NEW; elsif (TG_OP = 'UPDATE') then INSERT INTO book_audit_log ( book_id, old_row_data, new_row_data, dml_type, dml_timestamp, dml_created_by ) VALUES( NEW.id, to_jsonb(OLD), to_jsonb(NEW), 'UPDATE', CURRENT_TIMESTAMP, current_setting('var.logged_user') ); RETURN NEW; elsif (TG_OP = 'DELETE') then INSERT INTO book_audit_log ( book_id, old_row_data, new_row_data, dml_type, dml_timestamp, dml_created_by ) VALUES( OLD.id, to_jsonb(OLD), null, 'DELETE', CURRENT_TIMESTAMP, current_setting('var.logged_user') ); RETURN OLD; end if; END; $body$ LANGUAGE plpgsql
Чтобы функция book_audit_trigger_func
выполнялась после вставки, обновления или удаления записи книги
таблицы, мы должны определить следующий триггер:
CREATE TRIGGER book_audit_trigger AFTER INSERT OR UPDATE OR DELETE ON book FOR EACH ROW EXECUTE FUNCTION book_audit_trigger_func()
Функцию book_audit_trigger_func
можно объяснить следующим образом:
- переменная
TG_OP
определяет тип текущего выполняемого оператора DML. - ключевое слово
NEW
также является специальной переменной, которая сохраняет состояние текущей изменяющейся записи после выполнения текущего оператора DML. - ключевое слово
OLD
также является специальной переменной, которая сохраняет состояние текущей изменяющейся записи до выполнения текущего оператора DML. - функция
to_jsonb
PostgreSQL позволяет нам преобразовать строку таблицы в объект JSONB, который будет сохранен в столбцах таблицыold_row_data
илиnew_row_data
. - значение
dml_timestamp
равноCURRENT_TIMESTAMP
- в столбце
dml_created_by
задано значение переменной сеансаvar.logged_user
PostgreSQL, которая ранее была задана приложением для текущего зарегистрированного пользователя, например:
Session session = entityManager.unwrap(Session.class); Dialect dialect = session.getSessionFactory() .unwrap(SessionFactoryImplementor.class) .getJdbcServices() .getDialect(); session.doWork(connection -> { update( connection, String.format( "SET LOCAL var.logged_user = '%s'", ReflectionUtils.invokeMethod( dialect, "escapeLiteral", LoggedUser.get() ) ) ); });
Обратите внимание, что мы использовали УСТАНОВИТЬ ЛОКАЛЬНЫЙ
, так как мы хотим, чтобы переменная была удалена после фиксации или отката текущей транзакции. Это особенно полезно при использовании пула соединений .
Время тестирования
При выполнении инструкции INSERT в таблице книга
:
INSERT INTO book ( id, author, price_in_cents, publisher, title ) VALUES ( 1, 'Vlad Mihalcea', 3990, 'Amazon', 'High-Performance Java Persistence 1st edition' )
Мы видим, что в book_audit_log
вставлена запись, которая фиксирует инструкцию INSERT, только что выполненную в таблице book
:
| book_id | old_row_data | new_row_data | dml_type | dml_timestamp | dml_created_by | |---------|--------------|-----------------------------------------------------------------------------------------------------------------------------------------------|----------|----------------------------|----------------| | 1 | | {"id": 1, "title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 3990} | INSERT | 2020-08-25 13:19:57.073026 | Vlad Mihalcea |
При обновлении строки книга
таблица:
UPDATE book SET price_in_cents = 4499 WHERE id = 1
Мы видим, что новая запись будет добавлена в book_audit_log
с помощью book_audit_trigger_ |/:
| book_id | old_row_data | new_row_data | dml_type | dml_timestamp | dml_created_by | |---------|-----------------------------------------------------------------------------------------------------------------------------------------------|-----------------------------------------------------------------------------------------------------------------------------------------------|----------|----------------------------|----------------| | 1 | | {"id": 1, "title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 3990} | INSERT | 2020-08-25 13:19:57.073026 | Vlad Mihalcea | | 1 | {"id": 1, "title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 3990} | {"id": 1, "title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 4499} | UPDATE | 2020-08-25 13:21:15.006365 | Vlad Mihalcea |
При удалении строки книга
таблица:
DELETE FROM book WHERE id = 1
Новая запись добавляется в book_audit_log
с помощью book_audit_trigger_ |/:
| book_id | old_row_data | new_row_data | dml_type | dml_timestamp | dml_created_by | |---------|-----------------------------------------------------------------------------------------------------------------------------------------------|-----------------------------------------------------------------------------------------------------------------------------------------------|----------|----------------------------|----------------| | 1 | | {"id": 1, "title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 3990} | INSERT | 2020-08-25 13:19:57.073026 | Vlad Mihalcea | | 1 | {"id": 1, "title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 3990} | {"id": 1, "title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 4499} | UPDATE | 2020-08-25 13:21:15.006365 | Vlad Mihalcea | | 1 | {"id": 1, "title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 4499} | | DELETE | 2020-08-25 13:21:58.499881 | Vlad Mihalcea |
Потрясающе, правда?
Вывод
Существует множество способов реализации механизма ведения журнала аудита. Если вы используете Hibernate, очень простое решение-использовать энверы Hibernate .
Если вы не используете режим гибернации или хотите фиксировать события CDC независимо от того, как генерируются инструкции DML, то решение для запуска базы данных, подобное тому, которое представлено в этой статье, довольно просто реализовать. Сохранение старых и новых состояний строк в столбцах JSON-очень хорошая идея, поскольку она позволяет нам повторно использовать одну и ту же функцию, даже если структура исходной таблицы изменится.
Другой вариант-использовать специальную платформу CDC, такую как Debezium , которая извлекает события CDC из PostgreSQL WAL (Журнал предварительной записи). Это решение может быть очень эффективным, так как оно работает асинхронно, поэтому оно не влияет на текущие выполняемые транзакции OLTP. Однако настройка Debezium и запуск его в производство будут намного сложнее, поскольку Debezium также требует Apache Kafka и ZooKeeper.