Re: sending mail from Postgres - Mailing list pgsql-general

Tino Wildenhain wrote:

> How do you stop your server sending mail in case a transaction goes
> wild and is sending mails for every updated row in a some megs table?
>

It would not be smart to send a email via a trigger that updates or
inserts 1000s of rows would it?  All the times I have used PL Perl to
send a email that has NEVER been the case.

I agree that for a super busy server that is running a website or
something I probably would not do it that way(most would send the email
via PHP or whatever).  BUT for a GUI client server application(not web
based) it's more than acceptable especially when the emails are being
sent internally to a internal mail server where the connection/send time
is low.  It also does not have to be called from a trigger you could
just call it from another function.

It's not that difficult to catch and handle exceptions in a 8.1 or 8.0
plpgsql function, see next text from the docs:
***********************************************************************************
36.7.5. Trapping Errors

By default, any error occurring in a PL/pgSQL function aborts execution
of the function, and indeed of the surrounding transaction as well. You
can trap errors and recover from them by using a BEGIN block with an
EXCEPTION clause. The syntax is an extension of the normal syntax for a
BEGIN block:

[ <<label>> ]
[ DECLARE
     declarations ]
BEGIN
     statements
     -- Call PL Perl sendmail function here
EXCEPTION
     WHEN condition [ OR condition ... ] THEN
         handler_statements
     [ WHEN condition [ OR condition ... ] THEN
           handler_statements
       ... ]
END;

If no error occurs, this form of block simply executes all the
statements, and then control passes to the next statement after END. But
if an error occurs within the statements, further processing of the
statements is abandoned, and control passes to the EXCEPTION list. The
list is searched for the first condition matching the error that
occurred. If a match is found, the corresponding handler_statements are
executed, and then control passes to the next statement after END. If no
match is found, the error propagates out as though the EXCEPTION clause
were not there at all: the error can be caught by an enclosing block
with EXCEPTION, or if there is none it aborts processing of the function.
*********************************************************************************************

My point is that you can send a email from a PL perl or C function and
it will work just fine.  if you use error handling in your function, the
call to the sendmail function will be skipped, it's not that difficult.


Later,

Tony