deadlock in postgresql trigger function

More often than not, deadlocks occur because updates related to OLD and NEW are not enforced in a consistent order. Case in point:

IF TG_OP = 'UPDATE' THEN  IF (NEW.nightly_run_id IS NOT NULL) AND (NEW.nightly_run_id = OLD.nightly_run_id) THEN    -- stuff that seems fine  ELSE    IF NEW.nightly_run_id IS NOT NULL THEN      UPDATE nightly_runs ... WHERE id = NEW.nightly_run_id; -- lock    END IF;    IF OLD.nightly_run_id IS NOT NULL THEN      UPDATE nightly_runs ... WHERE id = OLD.nightly_run_id; -- lock    END IF;

Imagine two transactions:

• T1 acquires a lock on new.nightly_run_id = 1 and awaits a lock on old.nightly_run_id = 2
• T2 acquires a lock on new.nightly_run_id = 2 and awaits a lock on old.nightly_run_id = 1

Deadlock...

Force the order to avoid this kind of situation:

IF OLD.nightly_run_id = NEW.nightly_run_id THEN  -- stuff that seems fineELSIF OLD.nightly_run_id < NEW.nightly_run_id THEN  UPDATE nightly_runs ... WHERE id = OLD.nightly_run_id;  UPDATE nightly_runs ... WHERE id = NEW.nightly_run_id;ELSEIF NEW.nightly_run_id < OLD.nightly_run_id THEN  UPDATE nightly_runs ... WHERE id = NEW.nightly_run_id;  UPDATE nightly_runs ... WHERE id = OLD.nightly_run_id;ELSEIF OLD.nightly_run_id IS NOT NULL THEN  UPDATE nightly_runs ... WHERE id = OLD.nightly_run_id;ELSEIF NEW.nightly_run_id IS NOT NULL THEN  UPDATE nightly_runs ... WHERE id = NEW.nightly_run_id;END IF;

The same kind of change should occur for your other triggers where applicable. Bar other pathologies in your code, the deadlocks should then go away.