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

Personally I'd have your trigger put the necessary data into a queue to run the report then have some other process take that data off asynchronously. Either pop it in a table and do it yourself or use a robust distributed broker protocol platform like RabbitMQ or Apache ActiveMQ  or Kafka.

psql (9.6.0, server 11.3) on linux

Looking for ideas.  I want a trigger to...

1) compose an html report based on DB content

2) email the report to a dist list (dl = value of a table column)

If this will involve hybrid coding, I prefer PL/perl.  The linux env has both "mail" and "mutt" (if this is of any help).  

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. 

Thanks for any ideas :-)

psql (9.6.0, server 11.3) on linux

Looking for ideas.  I want a trigger to...

1) compose an html report based on DB content

2) email the report to a dist list (dl = value of a table column)

If this will involve hybrid coding, I prefer PL/perl.  The linux env has both "mail" and "mutt" (if this is of any help).  

> On May 8, 2020, at 09:26, David Gauthier <davegauthierpg@gmail.com> wrote:
>
> psql (9.6.0, server 11.3) on linux
>
> Looking for ideas.  I want a trigger to...
> 1) compose an html report based on DB content
> 2) email the report to a dist list (dl = value of a table column)

You probably *don't* want to actually send an email within a trigger; doing an operation that can block on an external
service(DNS, SMTP) within a trigger is asking for hard-to-diagnose trouble.  You probably don't even want to create the
HTML;that's adding a lot of time to the operation that fires the trigger. 

I'd probably set up the trigger to store the minimal data required to produce the HTML into a separate table, and then
havea background job query the table, create the HTML, and mail out the report.  If you don't want to periodically poll
thetable, you can use NOTIFY within the trigger to wake up a process that is waiting on NOTIFY. 

--
-- Christophe Pettus
   xof@thebuild.com

David G. Johnston <david.g.johnston@gmail.com> writes:

> On Fri, May 8, 2020 at 9:26 AM David Gauthier <davegauthierpg@gmail.com>
> wrote:
>
>> psql (9.6.0, server 11.3) on linux
>>
>> Looking for ideas.  I want a trigger to...
>> 1) compose an html report based on DB content
>> 2) email the report to a dist list (dl = value of a table column)
>>
>> If this will involve hybrid coding, I prefer PL/perl.  The linux env has
>> both "mail" and "mutt" (if this is of any help).
>>
>> 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.
>

Yep, exactly how I would do it as well. Personally, triggers are my last
choice. In some situations, they are the right choice and when you do
need them, keep them as small and simple as possible.

Others have mentioned the issues of using external calls inside a
trigger. In addition to the problem with waiting on external processes
to complete, you also have all the messy external work things to take
care of (like network down, server unavailable, etc).

The solution I've used in the past is to have procedures in the database
which generate the email report and insert that data into an email
table. I then have a shell level script (could be perl, javascript,
java, whatever) which looks in this table periodically and if it finds a
report which has not been sent, extract it, optionally format it and
send it. On successful completion, set a 'sent' flag on the report
record in the DB (or just delete it - I prefer to set a flag so that if
something failed unexpectedly, you still have the report).

There are ways you can trigger periodic activity in the database, but to
be honest, CRON is easy and reliable and avoids needing to add
additional extensions etc to the DB. Turning off the report, changing
the time etc, is a simple crontab edit. 

-- 
Tim Cross

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