CREATE FUNCTION statsrepo.alert(snap_id bigint)
RETURNS SETOF text AS
$$
DECLARE
  curr      statsrepo.snapshot; -- latest snapshot
  prev      statsrepo.snapshot; -- previous snapshot
  duration_in_sec  float8;
  rollback_per_sec float8;
BEGIN
  -- retrieve latest snapshot
  SELECT * INTO curr
    FROM statsrepo.snapshot
   WHERE snapid = snap_id;

  -- retrieve previous snapshot
  SELECT * INTO prev
    FROM statsrepo.snapshot
   WHERE snapid < curr.snapid AND instid = curr.instid;
  IF NOT FOUND THEN
    RETURN; -- no previous snapshot
  END IF;

  -- calculate duration for the two shapshots in sec.
  duration_in_sec :=
    extract(epoch FROM curr.time) - extract(epoch FROM prev.time);

  -- alert if rollbacks/sec is higher than 10.
  SELECT (c.rollbacks - p.rollbacks) / duration_in_sec
    INTO rollback_per_sec
    FROM (SELECT sum(xact_rollback) AS rollbacks
            FROM statsrepo.database
           WHERE snapid = curr.snapid) AS c,
         (SELECT sum(xact_rollback) AS rollbacks
            FROM statsrepo.database
           WHERE snapid = prev.snapid) AS p;
  IF rollback_per_sec > 10 THEN
     RETURN NEXT 'too many rollbacks in snapshots between ' ||
     prev.snapid || ' and ' || curr.snapid;
  END IF;
END;
$$
LANGUAGE plpgsql VOLATILE;