Триггер перед обновлением/вставкой работает только около 99,9% времени, после того как ранее работал на 100% надежно.

Содержание

Вопрос или проблема
Ответ или решение
Проблема с триггером "BEFORE UPDATE/INSERT" в вашей базе данных
1. Возможные причины несоответствия
2. Рекомендации по решению проблемы
Подводя итоги

Вопрос или проблема

В одной из баз данных моей организации есть таблица customerReview. Упрощенно, она выглядит так:

CREATE TABLE customerReview(
    customerReviewId SERIAL PRIMARY KEY,
    dateTimeCreated  TIMESTAMP WITHOUT TIME ZONE,
    dateTimeUpdated  TIMESTAMP WITHOUT TIME ZONE,
    businessID       INTEGER,
    reviewText       TEXT,
    reviewerIP       TEXT,
    isIPv6           BOOLEAN
)

Я не создавал таблицу, но добавил столбец isIPv6 вместе с триггером:

CREATE OR REPLACE FUNCTION review_ip_version()
    RETURNS TRIGGER AS
$BDY$
    IF NEW.reviewerip ~ '^[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}$' THEN
        NEW.isIPv6 := FALSE;
    ELSIF NEW.reviewerip ~ '^[0-9a-f]{0,4}(:[0-9a-f]{0,4}){2,7}$' 
        AND NEW.reviewerip !~ '::.*::|:::|[0-9a-f]{5,}|(:[0-9a-f]*){7}:' THEN 
        NEW.isIPv6 := TRUE
    END IF;

    RETURN NEW;
END;
$BDY$
LANGUAGE 'plpgsql';

Предполагается, что isIPv6 будет истинным, когда reviewerip соответствует стандартному паттерну IPv6, ложным, когда он соответствует стандартному паттерну IPv4, и null в противном случае. Это было протестировано на точность с различными шаблонами, включая NULL.

Триггер, который вызывает эту функцию, работает BEFORE INSERT OR UPDATE. Прямо перед добавлением триггера, в рамках той же транзакции, я обновил все существующие строки, чтобы они имели правильное значение isIPv6, используя тот же регулярное выражение. Это просто чтобы сказать, что я уверен, что значения столбца isIPv6 выглядели именно так, как и должны, после создания триггера. Тесты на следующий день также были успешными.

Триггер работал без проблем в течение примерно месяца, но именно сегодня что-то перестало работать. В частности, у нас есть 7 не удачных строк. Все 7 строк имеют reviewerip = NULL. 6 строк имеют isIPv6 = FALSE, в то время как 1 строка имеет isIPv6 = TRUE (значение должно быть NULL). Я знаю, что он перестал работать сегодня, так как процесс в другом месте, который зависит от корректности isIPv6, только что сломался. Триггер все еще на месте и работает для подавляющего большинства строк.

Моя рабочая гипотеза заключается в том, что какой-то процесс обновляет значения, но не инициирует триггер. К сожалению, моя организация не ведет журналы, а dateTimeUpdated ненадежен, так как он обеспечивается не на уровне базы данных, а через какой-то веб-процесс. Так что у меня не так много зацепок. Но есть ли какой-то неясный набор обстоятельств, при которых обновление может обойти триггер BEFORE INSERT OR UPDATE в таблице?

Ответ или решение

Проблема с триггером "BEFORE UPDATE/INSERT" в вашей базе данных

Ваша ситуация действительно вызывает интерес, и я постараюсь детально разобрать возможные причины, почему триггер, обрабатывающий поля в таблице customerReview, перестал работать на 100%. Рассмотрим ваш случай и выявим возможные проблемы, которые могут привести к тому, что некоторые обновления не инициируют триггер.

1. Возможные причины несоответствия

NULL значения: Как вы уже отметили, проблема возникла в основном с NULL значениями в поле reviewerip. Ваша текущая логика триггера не обрабатывает случай, когда reviewerip равен NULL. Это важный момент, так как в результате, когда у вас имеется:
- reviewerip = NULL → isIPv6 не обновляется и остается тем, что было до этого (True/False). Как следствие, одна из записей имеет isIPv6 = TRUE, хотя это не должно быть возможным.
Неявные обновления: Как вы правильно заметили, существует вероятность, что какой-то процесс обновляет записи в таблице без вызова триггера. Например:
- Использование SQL операторов, которые могут обойти триггеры, таких как UPDATE с использованием прав на уровне записи, что может вызвать временное состояние, где триггер фактически не срабатывает.
Другая бизнес-логика: Если существуют элементы кода, которые напрямую обновляют поле isIPv6, то это также может привести к несоответствию. Проверьте, нет ли в бизнес-логике автоматических обновлений данного поля.

2. Рекомендации по решению проблемы

Обработка NULL: Измените функцию триггера, чтобы она явно обрабатывала случаи, когда reviewerip равно NULL. Например, можно добавить дополнительную проверку:

IF NEW.reviewerip IS NULL THEN
    NEW.isIPv6 := NULL;
ELSEIF NEW.reviewerip ~ '^[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}$' THEN
    NEW.isIPv6 := FALSE;
ELSEIF NEW.reviewerip ~ '^[0-9a-f]{0,4}(:[0-9a-f]{0,4}){2,7}$' 
   AND NEW.reviewerip !~ '::.*::|:::|[0-9a-f]{5,}|(:[0-9a-f]*){7}:' THEN 
    NEW.isIPv6 := TRUE;
END IF;

Логирование изменений: Даже если ваш проект сейчас не предусматривает логи, подумайте о том, чтобы реализовать систему мониторинга базы данных. Это может помочь выявить аномальные изменения и отследить, какие процессы инициировали обновления.
Используйте триггер для проверки isIPv6: Если возможно, вы можете создать триггер на BEFORE INSERT OR UPDATE, чтобы проверять и корректировать значения isIPv6 на случай, если они ошибочны.

Подводя итоги

Ваш случай иллюстрирует, насколько важно тщательно проверять бизнес-логику и триггеры в базе данных. Обработка некорректных данных и поддержание целостности информации — ключевая задача для каждой организации. Рассмотрите предложенные шаги для устранения возникающих несоответствий и устранения причин аномальных значений в столбце isIPv6.