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
- 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 - after table lock check if table is different from the current visible snapshot, then raise serialization failure – I have no idea how do it
- 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 requiredREAD COMMITTED
I was able to fix this mode by using table lockREPEATABLE 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();
Best Answer
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: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 sameNEW.val
in thechild
table. And all such rows are visible to the current transaction (even inREPEATABLE READ
) because it started with the lock.You do not even need a foreign key constraint between
child
andparent
(though it won't hurt). You can just start with an emptyparent
table.Waring: not reliable as trigger in
REPEATABLE READ
mode! See below.The manual:
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 inREPEATABLE 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
constraintUntested, no more time ...
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 existingval_nr
and fill gaps, which can still fail inREPEATABLE READ
mode, but not silently. You'd get a duplicate key violation error.