PostgreSQL – How to Set Up DB Alert on Values Change

postgresqlpsql

How to make notification when some conditions written by user are true.

I have one table DATA which has 3 rows.

name  value
----  -----
a1    10
a2    20
a3    30

This table changes some values every 1-10 minutes.

Example: a1=12 , a2=21

ALERT:
if a1>11 and a2>20 send me some message.

Question: How to check when a value in DATA fulfils the conditions in alert, and then my php/python environment takes any action on this alarm?

My idea:

  • After update on DATA table run trigger that will check if ALERT exist and set active on it (how to check when DATA id a1=12 and then check if there is some alert like a1>11)
  • Run every 1 minute process that checks if ALERT is active and execute it.

I don't now where to start…

I will use: PostgreSQL, php, cron

Best Answer

You have multiple choices here.

  • writing a crontab that will check every minutes your data (that's what you propose).
  • using NOTIFY/LISTEN with trigger like this :
CREATE OR REPLACE FUNCTION f_check_data_value()
  RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  SELECT pg_notify('data', NEW.value);
END;
$$;

CREATE TRIGGER check_data_values_trigger
AFTER INSERT OR UPDATE
  ON mytable
FOR EACH ROW EXECUTE PROCEDURE f_check_data_value();

You'll then have to write a listener (in PHP if it's what you are using). I haven't used php inn a while but the php.net documentation is a good place to start I think : http://php.net/manual/fr/function.pg-get-notify.php

Related Question