How to Send Email from Postgresql Trigger

How can I send email from PostgreSQL trigger?

See the excellent-as-usual depesz article, and pg-message-queue.

Sending email directly from the database may not be a great idea. What if DNS resolution is slow and everything hangs for 30 seconds then times out? What if your mail server is having a wobbly and takes 5 minutes to accept messages? You'll get database sessions hung up in your trigger until you're at max_connections and suddenly you can't do anything but wait or start manually cancelling transactions.

What I'd recommend is having your trigger NOTIFY a LISTENing helper script that remains permanently running and connected to the DB (but not in a transaction).

All your trigger has to do is INSERT a row into a queue table and send a NOTIFY. Your script gets the NOTIFY message because it has registered to LISTEN for it, examines the queue table, and does the rest.

You can write the helper program in whatever language is convenient; I usually use Python with psycopg2.

That script can send the email based on information it finds in the database. You don't have to do all the ugly text formatting in PL/PgSQL, you can substitute things into a template in a more powerful scripting language instead, and just fetch the variable data from the database when a NOTIFY comes in.

With this approach your helper can send each message and only then remove the info from the queue table. That way if there are transient problems with your mail system that causes sending to fail, you haven't lost the info and can continue to attempt to send it until you succeed.

If you really must do this in the database, see PgMail.

How to create a trigger to send email

While the database engine may initiate e-mail sending it should never do the sending itself.
Only some short (quick) and error-free actions should be done within the database server.

There should be another application/service/daemon running, which should be doing the e-mails based on the data prepared in SQL realm. The question is when this sender application should be triggered.

The database-agnostic way would be polling the database by scheduled time intervals, which traditionally nicknamed as "cron" actions, mentioned by Rajiv Shah. Once a minute or once a second or once an hour - by your choice.

A Firebird-specific way would be using POST_EVENT <string constant> command. It can be used instead of time-based polling or together with.

Here is the example:

CREATE TRIGGER POST_NEW_ORDER FOR SALES
ACTIVE AFTER INSERT POSITION 0
AS
BEGIN
  POST_EVENT 'new_order';
END

https://firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-psql-coding.html#fblangref25-psql-postevent

How your program would subscribe to those events would be dependent upon programming language and Firebird-accessing library. As far as i understand PHP would probably fit poorly there, as it is more tailored to run short scripts by daemons' requests, rather than being a continuously running daemon itself. Though perhaps PHP gurus can have it both ways. I also don't know if PHP has support for the Firebird events, it does not seem to be a priority for scripting language.

You can read more about Events by googling for Firebird POST_EVENT, among many links there will be for example those:

• Is it possible to use arguments with firebird events : POST_EVENT 'event_name' + string args?
• https://mikejustin.wordpress.com/2012/11/06/firebird-database-events-and-message-oriented-middleware/
• https://www.firebirdsql.org/file/documentation/papers_presentations/Power_Firebird_events.pdf

Send e-mail from a trigger

First you need to set up database mail - if you haven't done so, this question might help:

• Scripting setup of database mail

Then you need a trigger:

CREATE TRIGGER dbo.whatever
ON dbo.wherever
FOR INSERT
AS
BEGIN
    SET NOCOUNT ON;

    IF EXISTS (SELECT 1 FROM inserted WHERE speed > 100)
    BEGIN
        EXEC msdb.dbo.sp_send_dbmail
          @recipients = 'whoever@yourcompany.com', 
          @profile_name = 'default',
          @subject = 'Someone was speeding', 
          @body = 'Yep, they sure were.';
    END
END
GO

Now, you're probably going to say you want data from the insert to be actually be included in the e-mail. And your first inclination is going to be to declare some local variables and assign them from inserted - this doesn't work because your trigger could be responding to a multi-row insert. So the right way to do this is:

CREATE TRIGGER dbo.whatever
ON dbo.wherever
FOR INSERT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @body NVARCHAR(MAX) = N'';

    SELECT @body += CHAR(13) + CHAR(10) + RTRIM(some_col) FROM inserted;

    IF EXISTS (SELECT 1 FROM inserted WHERE speed > 100)
    BEGIN
        EXEC msdb.dbo.sp_send_dbmail
          @recipients = 'whoever@yourcompany.com', 
          @profile_name = 'default',
          @subject = 'At least one person was speeding', 
          @body = @body;
    END
END
GO

That all said, I am not a big fan of sending e-mail from a trigger. Even though database mail uses service broker and so is asynchronous, I would be much more inclined to populate a queue table, and have a background thread that comes around and sends all of the appropriate e-mails. The twothree nice things about this are:

1. you minimize the potential delays in committing the outer transaction that fired the trigger - the more complicated your logic in the trigger, the slower you make that process.
2. since it is probably not essential that the e-mail is sent the microsecond the row is inserted, you can easily fluctuate the timing of the background process - this avoids having to check the table very minute, all day, when very few times it will ever have to actually do anything.
3. As @goodeye pointed out, keeping this process separate can prevent errors in the e-mail part of the process from interfering with the original DML (in their case, an invalid parameter to sp_send_dbmail - which I inadvertently suggested - prevented the insert).

PL/Perl send mail in Postgresql

Just because you can doesn't mean you should. There are better ways to do this. Don't do it directly from a PL. If you want to ignore my warnings, use PL/PerlU and write it like you would any other email client. You can use any CPAN modules you like that make your life easier.

Two reasons not to:

1) What if your transaction aborts/rolls back? You have sent the email but made no corresponding change to the db. You are doing non-transactional stuff inside a transaction.

2) What if your email hangs waiting for a response until you get a tcp timeout after 2 min? Are you going to forget about emailing the customer? Abort the transaction (can't send email, can't say we have shipped the part!)?

This is a bad idea. Don't do it. Thank PostgreSQL for this error and move it out into another daemon.

A much better approach is to use LISTEN and NOTIFY, and queue tables. You can then create a table like this:

CREATE TABLE email_queue (
    id serial not null unique,
    email_from text,
    email_to text not null,
    body text not null
); 

CREATE FUNCTION email_queue_trigger() RETURNS TRIGGER 
LANGUAGE PLPGSQL AS $F$
    BEGIN
        NOTIFY emails_waiting;
    END;
$F$;

Then have your stored procedure insert into that table.

Then, have a second client app which LISTENs on the emails_waiting listens (sql statement LISTEN emails_waiting) and then does as follows:

1. Checks whether there are records in the email_queue. If not go to 3.
2. reads data, sends email, deletes the record, and commits.
3. When queue is empty sleeps for x seconds
4. On wakeup, checks for async. notifications (depends on client libraries, check docs). If there are, go to 1, if not, go to 3.

This allows your emails to be queued for sending in your transaction and for this to be automatically passed to another application which can then connect with the MTA if your choice.

That second client app can be written in the language of your choice, using whatever tools you know. It has the advantage of doing all network stuff out of the transaction, so if you are sending via a second SMTP server, and the connection hangs, your whole database transaction doesn't wait for 2 min for it to time out and abort the transaction. It is also thus safer against future changes in requirements.

---

Related Topics