1

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();
2
  • If a table lock fixes the problem in READ COMMITTED, why does that not work in REPEATEBLE READ (which is "stronger" than READ COMMITTED)? – Lennart Apr 1 '18 at 14:26
  • @lennart repeatable read creates snapshot on transaction start, so when transaction acquires lock on the table, it still gets outdated data. For the check is required that I am able to read valid data or at least detect that I don't see valid data. – Tomas Apr 1 '18 at 18:35
2

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:

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.

2
  • Thank you but I think it does not answer my question. So with regular constraints, I can do anything (like reading from the table without previous locking), then do some action and commit - at some point it fails or pass and i can be sure that integrity of data is ensured. Instead you suggest separate transaction. With this inside the trigger's check is very fragile (I wanted to avoid it). Also I don't see how locking itself can ensure that i will get always reliable data in REPEATABLE READ mode for checking constrains by trigger. – Tomas Apr 2 '18 at 22:54
  • @Tomas: I thought I had an answer, forgetting it's a trigger function that's not typically run as 1st statement in the transaction. I didn't delete it because it had enough substance to be useful, notwithstanding. Of course, after you changed the question, my idea with a parent table is not applicable any more. An autonomous transaction (well, emulating one) should be able to solve the problem of invisible changes in REPEATABLE READ, but at the cost of performance and introducing other complications and points of failure ... – Erwin Brandstetter Apr 2 '18 at 23:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.