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.
- 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
SERIALIZABLEseems working by design - no locking is required
READ COMMITTEDI was able to fix this mode by using table lock
REPEATABLE READNo idea how to fix this mode
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;
CREATE TRIGGER validate BEFORE INSERT OR UPDATE ON test FOR EACH ROW EXECUTE PROCEDURE verify();