Предположим, у вас есть таблица, в которой каждая строка содержит различные характеристики клиентов. Эти функции могут быть статическими (например, время создания учетной записи, пол и т. д.) и/или динамическими (например, «количество поисков», «последнее посещение» и т. д.). Одна из проблем, возникающих при ведении такой таблицы, заключается в том, что разные процессы обновляют эти функции в разное время. Например, некоторые динамические функции могут работать в режиме реального времени, тогда как другие могут поступать из Hadoop и иметь 24-часовую задержку. С точки зрения отладки наличие таблицы, которая обновляется различными процессами, может быть кошмаром, поскольку нет возможности путешествовать во времени и определять, как значения функций менялись с течением времени. Очевидное решение — вести журнал изменений.
Хотя существует много способов создать такой журнал изменений, этот пост посвящен использованию хранимых процедур базы данных для автоматического создания журнала изменений при вставке или обновлении функций. Хранимая процедура базы данных позволяет определить пользовательскую обработку вставленных/обновленных данных в базу данных и запускается во время обычных операций CRUD (создание-чтение-обновление-удаление). Мне особенно нравится этот подход, так как он более тесно интегрирован с базами данных и сохраняет чистоту и компактность прикладного уровня.
Есть три шага, чтобы заставить хранимую процедуру работать. В сообщении ниже приведен пример трех шагов. В приведенном ниже примере цель состоит в том, чтобы наблюдать за любой операцией вставки или обновления в одной таблице user_features и создать запись в таблице changelog, чтобы мы могли следить за тем, как функции в таблице user_features менялись с течением времени.
Шаг 1. Определите таблицу для наблюдения
Чтобы продемонстрировать, как работают хранимые процедуры, давайте создадим две таблицы. Как определено ниже, таблица user_featurestable содержит различные функции о клиентах. Мы также определяем другую таблицу, журнал изменений, чтобы фиксировать изменения (когда и как) в функциях клиента.
-- Define customer feature table
CREATE TABLE user_features (
id INTEGER PRIMARY KEY, -- customer_id
account_created_at DATE, -- ACCOUNT CREATION DATE
search_ct INTEGER, -- NUMBER OF SEARCHES
click_ct INTEGER -- NUMBER OF CLICKS
)
-- Define changelog table to capture changes
CREATE TABLE changelog (
customer_id INTEGER NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
data JSONB NOT NULL -- STORE FEATURES THAT WERE UPDATED
)
Шаг 2. Определите задачу для выполнения
Далее, как показано ниже, мы создаем функцию, которая будет прослушивать изменения в user_featurestable и добавлять запись в changelogtable. Функция имеет доступ ко многим полезным переменным, но две самые важные — это «СТАРАЯ» и «НОВАЯ». Как следует из названия, переменная «OLD» содержит строку базы данных, которая будет обновляться перед внесением изменений. Переменная NEW содержит копию строки после изменений. Ниже мы конвертируем обновленную строку в большой двоичный объект JSON и удаляем «id», поскольку мы храним его отдельно. Это станет ясно, когда мы вставим строки в нашу таблицу характеристик клиентов ниже.
CREATE OR REPLACE FUNCTION log ()
RETURNS TRIGGER
AS $BODY$
BEGIN
IF TG_OP IN ('UPDATE', 'INSERT') THEN
INSERT INTO changelog (customer_id, data)
VALUES (
NEW.id,
JSON_STRIP_NULLS(ROW_TO_JSON(NEW))::JSONB - 'id'
);
END IF;
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql
VOLATILE
SECURITY DEFINER
COST 100;
Шаг 3. Определите триггеры для подключения наблюдаемой таблицы и выполняемой задачи
Наконец, мы создаем отдельные триггеры для привязки вышеуказанной функции к операциям insert и update в таблице user_features.
CREATE TRIGGER log_insert AFTER INSERT ON user_features FOR EACH ROW EXECUTE PROCEDURE add_changelog (); CREATE TRIGGER log_update AFTER UPDATE ON user_features FOR EACH ROW WHEN (OLD.* IS DISTINCT FROM NEW.*) EXECUTE PROCEDURE add_changelog ();
Теперь начинается самое интересное. Вставка новой записи или обновление существующей строки в таблицеcustomer_feature автоматически создает новую запись в таблице журнала изменений.
INSERT INTO customer_features values (1, '2020-10-01', 1, 0);
SELECT * FROM changelog;
customer_id | created_at | data
------------+---------------+-----------
1 | 2021-01-22...| {"click_ct": 0, "search_ct": 1, "account_created_at": "2020-10-01"} <-- Note this record was automatically created on insert
UPDATE customer_features SET search_ct = 3 WHERE id = 1;
SELECT * FROM changelog;
customer_id | created_at | data
-------------+---------------+------------
1 | 2021-01-22 23.. | {"click_ct": 0, "search_ct": 1, "account_created_at": "2020-10-01"}
1 | 2021-01-22 23.. | {"click_ct": 0, "search_ct": 3, "account_created_at": "2020-10-01"} <-- note this record was automatically created on update
Это здорово. Вы можете проявить творческий подход к хранимым функциям. Например, можно заметить, что приведенный выше журнал изменений содержит копии всех функций, даже тех, которые не изменились. Можно легко изменить сохраненные функции, как показано ниже, чтобы захватить только те функции, которые изменились. Однако помните, что наличие сложных хранимых процедур увеличит время работы CURD, а также потребует больше ресурсов ЦП.
-- THIS IS TRYING TO BE SPACE EFFICIENT
-- WE ONLY STORE FEATURES THAT HAVE BEEN UPDATED
CREATE OR REPLACE FUNCTION add_changelog ()
RETURNS TRIGGER
AS $BODY$
BEGIN
IF TG_OP = 'UPDATE' THEN
INSERT INTO changelog (customer_id, data)
SELECT
NEW.id,
JSON_OBJECT(ARRAY_AGG(NEW2.key), ARRAY_AGG(NEW2.value))
FROM
JSON_EACH_TEXT(ROW_TO_JSON(OLD)) OLD2
RIGHT JOIN JSON_EACH_TEXT(ROW_TO_JSON(NEW)) NEW2
ON OLD2.key = NEW2.key
WHERE
((OLD2.value IS NOT NULL AND NEW2.value IS NULL)
OR (OLD2.value IS NULL AND NEW2.value IS NOT NULL)
OR OLD2.value <> NEW2.value)
AND NEW2.key <> 'id'
;
ELSIF TG_OP = 'INSERT' THEN
INSERT INTO changelog (customer_id, data)
VALUES (NEW.id, JSON_STRIP_NULLS(ROW_TO_JSON(NEW))::JSONB - 'id');
END IF;
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql
VOLATILE
SECURITY DEFINER
COST 100;x
