PL/Perl send mail in Postgresql

Question

Possible Duplicate:
psql trigger send email

I using PL/Perl language to send mail in Postgresql. When dataset table upload_status change to published, and it will send mail to the author's email address in dataset table. And in letter contains some records in this author in dataset. The dataset PK is identifier.

like send from xx@mail.com to (dataset.email)@mail.com

Dear Dr. (dataset.author)

your...... (dataset.product) have already .......

so how to write the function using PL/Perl and trigger function.

Thanks, I using this method http://evilrouters.net/2008/02/01/send-email-from-postgresql/

mydb=# CREATE OR REPLACE FUNCTION mydb_mytable_insert_send_mail_function()
mydb-# RETURNS "trigger" AS
mydb-# $BODY$
mydb$# use Mail::Sendmail;
mydb$#
mydb$# $message = "A new entry has been added to the 'mytable' table.\n\n";
mydb$# $message .= "The new name is: $_TD->{new}{name}\n\n";
mydb$#
mydb$# %mail = ( From => $_[0], To => $_[1], Subject => $_[2], Message => $message);
mydb$#
mydb$# sendmail(%mail) or die $Mail::Sendmail::error;
mydb$# return undef;
mydb$# $BODY$
mydb-# LANGUAGE 'plperlu' VOLATILE;

mydb=# CREATE TRIGGER mydb_mytable_insert_send_mail_trigger
mydb=# AFTER INSERT ON mytable
mydb=# FOR EACH ROW
mydb=# EXECUTE PROCEDURE mydb_mytable_insert_send_mail_function('from@domain.com',          'to@domain.com', 'subject here');

but it show an error has occurred Can't locate Mail/Sendmail.pm in @INC(@INC contains: /usr/...

how to create the trigger and trigger function to send mail using pl/perl. And how to select some record in database and add into email.like author's name — AntiGMO, Sep 03 '12 at 08:25

Chris Travers · Accepted Answer · 2012-09-03T08:49:18.737

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:

Checks whether there are records in the email_queue. If not go to 3.
reads data, sends email, deletes the record, and commits.
When queue is empty sleeps for x seconds
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.

I expressed similar sentiments here: http://stackoverflow.com/a/12003516/398670, albeit with some different details. The whole thing is such a bad idea. — Craig Ringer, Sep 03 '12 at 23:43

score 6 · Answer 2 · answered Sep 03 '12 at 08:34

Short answer: don't.

Long answer: use a separate process to send the emails and just record the requirement to send in the database.

Sending an email can fail temporarily, it can fail permanently. Sooner or later you will want to pause sending emails without halting the rest of the database. Then someone will want the message template changed, and you don't really want to update the database just for that.

Your solution can be as simple as a message_queue table and a cron-job running once a minute or a daemon using LISTEN/NOTIFY. I've done this a few times now and having a separate email sender has always been the better option.

PL/Perl send mail in Postgresql

2 Answers2

Linked