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();
READ COMMITTED
, why does that not work inREPEATEBLE READ
(which is "stronger" than READ COMMITTED)? – Lennart Apr 1 '18 at 14:26