Re: sending mail from Postgres

Tony Caduto schrieb:
> Tino,
> I am talking about corporate apps, there would never be thousands of
> emails in the email servers inbound queue from just a few database
> applications. (anyway a thousand emails in a Postfix queue is nothing)
>
> Sometimes it is best to send the email right from the client app, but in
> many situations sending a email from the client application may be
> blocked by corporate firewall restrictions etc, so sending the email
> from the server via a trigger or function is the best bet.

Been there. Done that. My email was just about to make you
aware of the problems. You seem "Oh, sending mail from database
is a good idea, lets do that" but actually its not that easy
(but easy done with one of the pl/*u - languages)

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?

> The email should be the last thing that is called, and in postgresql 8.1
> you can use error handling in your function if you so desire to avoid
> sending a bogus email if there is a problem.

I'd be surprised how you can avoid sending mail in a transaction but
get it back if the transaction rolls back. Maybe with a queue? But
then - why not just do all the processing outside the database?

> And you can code a trigger that fires when there is a difference of 60
> days between dates(I have apps that do so...,and the trigger sends a email)
>
> You have your opinion and I have mine, but mine is backed by real
> applications running in a corporate environment, and the situations you
> describe NEVER occur, so if some one want's to send a email from
> Postgresql I don't think they should be turned back by FUD.

*hollow lough* No, we are all playing with toy applications ;) Get real
man, get real :)

I wrote "you can do that" but "its not recommended".

Hint: client applications can run on servers too :-)
So much for "firewall rules" and all the like.

I'd recommend a small easy controllable script which receives
the message signals thru one connection to the server and
LISTEN/NOTIFY and then checks consistency (when did I last
send similar mail etc...) and handles all the mail sending.
This frees up the server early (NOTIFY is rather cheap)
And leaves you with a simple "kill" in case something
goes wrong with your script, w/o disturbing your database
application.

This idea has grown in the practice of a large corp.
application in case you wonder...

Nice days
Tino