How to write validation trigger which works with all isolation levels

Question

Background

I need to write validation trigger for a table. Due to nature of validation I can not use builtin integrity checks. For purpose of this question let's say that validation counts number of rows with specific properties and number of this rows has to be less than 5. In fact my conditions are more complicated (there is a relation between two columns). Please consider validation as blackbox with only one property, it uses whole test table, no data from outside.

I don't want limit usage of database isolation levels. SERIALIZABLE works by design but other two should be also possible to use.

The main idea is, that usage of the trigger should be as easy as usage of other constraints.

Ideas

1. raise error when repeatable read mode is used (can be detected by current_setting('transaction_isolation'). This is the least preferred solution but I least it ensures data integrity
2. after table lock check if table is different from the current visible snapshot, then raise serialization failure - I have no idea how do it
3. Use another transaction inside trigger (after table is locked) to see if data are unchanged or not autonomous transactions

Trigger example - my current best solution

• SERIALIZABLE seems working by design - no locking is required
• READ COMMITTED I was able to fix this mode by using table lock
• REPEATABLE READ No idea how to fix this mode

Code

CREATE OR REPLACE FUNCTION verify() RETURNS trigger
AS $$
DECLARE
    count integer;
BEGIN
    LOCK TABLE test IN EXCLUSIVE MODE; --fixes races in READ COMMITTED mode
    count := (SELECT count(*) FROM test where val = NEW.val);

    IF count >= 5 THEN -- this is simplified for example, validation real validation uses more columns
        RAISE EXCEPTION '% Already present', NEW.val USING ERRCODE = 'integrity_constraint_violation';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

registration

CREATE TRIGGER validate BEFORE INSERT OR UPDATE
 ON test FOR EACH ROW EXECUTE PROCEDURE verify();

Answer 1

You can solve this for any isolation level by taking an exclusive lock on a row in a parent table - which is much cheaper and less intrusive than locking the whole table like you do currently.

If you don't have one already (which is a typical case), just create a simple table with some kind of UNIQUE constraint, a PK serves nicely:

CREATE TABLE parent (val integer PRIMARY KEY);  -- match data type

Achieve the lock with an UPSERT statement (as first statement in the transaction!) which either inserts a row in the parent table (holding an exclusive lock automatically) or takes a lock on an existing row. This way, only a single transaction at a time can write to rows with the same NEW.val in the child table. And all such rows are visible to the current transaction (even in REPEATABLE READ) because it started with the lock.

You do not even need a foreign key constraint between child and parent (though it won't hurt). You can just start with an empty parent table.

Waring: not reliable as trigger in REPEATABLE READ mode! See below.

CREATE OR REPLACE FUNCTION verify()
  RETURNS trigger AS
$func$
BEGIN   
   INSERT INTO parent AS p (val)    -- insert parent
   VALUES (NEW.val)
   ON     CONFLICT (val) DO UPDATE  -- if already there ...
   SET    val = NEW.val
   WHERE  FALSE;                    -- never executed, just locks the row

   IF (SELECT count(*) >= 5 FROM child c WHERE c.val = NEW.val) THEN
      RAISE EXCEPTION '% already present 5 times', NEW.val USING ERRCODE = 'integrity_constraint_violation';
   ELSE
      RETURN NEW;                   -- proceed with INSERT / UPDATE
   END IF;
END
$func$  LANGUAGE plpgsql;

The manual:

all rows will be locked when the ON CONFLICT DO UPDATE action is taken.

When inserting / updating multiple rows, make sure the rows are in consistent order to avoid deadlocks.

On second thought, while cheaper than your current trigger function, this can still fail because a trigger function is not necessarily the first statement in the transaction. Concurrent transactions may have committed between transaction start and the time the lock is taken. And any such rows in the child table remain invisible in REPEATABLE READ mode, making the count fail.

You would have to encapsulate each write on one or more rows with the same val in a separate transaction, starting with the UPSERT to take the lock first.

Related:

• How to perform conditional insert based on row count?
• How to use RETURNING with ON CONFLICT in PostgreSQL?
• Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?

Alternative: add a number column and a UNIQUE constraint

Untested, no more time ...

ALTER TABLE child
   ADD COLUMN val_nr int NOT NULL
 , ADD CONSTRAINT child_max5_per_val CHECK (val_nr BETWEEN 1 AND 5)
 , ADD CONSTRAINT child_val_nr UNIQUE (val, val_vr);

These constraints enforce your rules in all transaction isolation modes. But you need to assign a val_nr between 1 and 5 for every row. After taking the lock, you can look up existing val_nr and fill gaps, which can still fail in REPEATABLE READ mode, but not silently. You'd get a duplicate key violation error.