postgresqltriggers

Trigger before update / insert is only working about 99.9% of the time, after previously working 100% reliably


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?


Solution

  • 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.