In one of my organization's databases there's a table customerReview
. Simplified, it looks like:
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
)
I didn't create the table but I did add the isIPv6
column together with a trigger function:
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';
The intention is for isIPv6
to be true when reviewerip
matches a valid IPv6 pattern, false when it matches a valid IPv4 pattern, and null otherwise. This was tested to perform accurately with various patterns including NULL.
The trigger which calls this function is BEFORE INSERT OR UPDATE
. Right before adding the trigger, within the same transaction, I updated all of the existing rows to have the correct isIPv6
value, using the same regex. This is just to say that I'm certain the isIPv6
column values looked exactly as they should after the trigger was created. Tests the next day were also successful.
The trigger worked without issue for about a month, but just today something stopped working. Specifically we have 7 failing rows. All 7 rows have reviewerip = NULL
. 6 rows have isIPv6 = FALSE
, while 1 row has isIPv6 = TRUE
(value should be NULL). I know it stopped working today since a process elsewhere that depends on isIPv6
being correct just broke. The trigger is still in place and working for the vast majority of rows.
My working hypothesis is that some process is updating values, but without initiating the trigger. Unfortunately my organization doesn't keep log tables, and dateTimeUpdated
isn't reliable since it's enforced not at the database level but through some web process. So I'm not left with a lot to go off of. But is there a obscure set of circumstances where an update can circumvent a BEFORE INSERT OR UPDATE
trigger on a table?
Your trigger function has syntax errors: it is missing the initial BEGIN
and a semicolon.
After fixing these errors, the case seems clear to me: if I update a row with a value for reviewerip
that matches neither pattern, like NULL, NEW.isIPv6
doesn't get changed, so it just retains the value it had before the update.
You will have to improve the trigger function, for example by adding an ELSE
branch that sets the column to NULL.