PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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?

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    For version 16 and later, you can use the function pg_input_is_valid() to check if it is an inet. And when it is, you can use the function family() to check if it’s a 4 or a 6.

    Simple example:

    SELECT input,
    CASE
        WHEN pg_input_is_valid(input, 'inet')
            THEN family(CAST(input AS inet)) = 6
        ELSE FALSE
    END
FROM    (VALUES ('127.0.0.1'),('2001:db8::8a2e:370:7334'),('foo')) s(input);

    A check like this can be embedded in your trigger function, without the usage of a regex.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search