Postgresql LISTEN / NOTIFY recap

Question

I created the following trigger to track all the changes on a postgres table.

DROP TRIGGER tr_request_update_notify ON requests;

CREATE OR REPLACE FUNCTION request_update_notify() RETURNS trigger as $$
BEGIN  
  PERFORM pg_notify('request_update_notify', json_build_object('table', TG_TABLE_NAME, 'id', NEW.id, 'event', NEW.event, 'type', TG_OP)::text);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;


CREATE TRIGGER tr_request_update_notify AFTER UPDATE or INSERT ON requests FOR EACH ROW EXECUTE PROCEDURE request_update_notify();

Another application will listen to the connection and apply suitable treatment for each event.

If an event happens and my application is not up, the event will never be processed. Is there a way to have a recap of all missed notification?

Answer 1

Notification are not stored anywhere, they are just sent to whatever session is listening on the same notification channel. But seeing that you are in a database, why not store the notification in a table and then the listeners simply poll that table when they are active. You can then also use straight json instead of casting it to text. Not as "automatic" as NOTIFY/LISTEN but otherwise pretty fool-proof.

CREATE OR REPLACE FUNCTION request_update_notify() RETURNS trigger as $$
BEGIN  
  INSERT INTO my_notifications (channel, message_time, notification)
  VALUES ('request_update_notify', CURRENT_TIME,
          json_build_object('table', TG_TABLE_NAME,
                            'id',    NEW.id,
                            'event', NEW.event,
                            'type',  TG_OP)
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;