PL/Perl send mail in Postgresql [duplicate]

2020-08-01 12:02发布

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');

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.

回答2:

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.