Re: Best way to use trigger to email a report ?

On 09/05/20, Tim Cross (theophilusx(at)gmail(dot)com) wrote:
> David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> > On Fri, May 8, 2020 at 9:26 AM David Gauthier <davegauthierpg(at)gmail(dot)com>
> > wrote:

> >> The idea is to send a report to the list when all the data has been
> >> collected for a particular job and the final status of the job is updated
> >> as a col of a rec of a certain table. Probably a post update trigger.
> >
> > I'd probably limit the trigger to checking for the completion of the data
> > collection and inserting a record into a "email job" table. Then I'd have
> > cron on a linux machine periodically run a script that queries the "email
> > job" table for work, perform the work, and then either flag the work as
> > done or remove the job record.

We have gone for a solution using triggers with pgmemcached and
supervisord.

Supervisord loops, checking memcached for updates to any in our large
cluster of databases periodically, eg every 15 seconds, and then picks
up the messages to send from any databases with messages pending.

Here is an example trigger:

CREATE OR REPLACE FUNCTION trigger_send_message() RETURNS TRIGGER AS $$
BEGIN
IF NEW.status = 'sendme' THEN
PERFORM extensionscheme.memcache_set(current_database(), '1');
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

In our case, with many databases, we wished to remove the connection
overhead for many NOTIFYs. The above arrangement means the supervisord
process only makes a connection to the client database if messages are
pending.

Rory