Re: Running OS-level programs from Postgres?

Andrew Sullivan <ajs(at)crankycanuck(dot)ca> writes:
> On Wed, Jun 20, 2007 at 12:55:23PM -0700, Steve Atkins wrote:
>> Another approach, and one that can be more robust in the case
>> of external failures, is to have the trigger put the message it wants
>> to send into a queue table and have an external process that
>> monitors the table (via polling or listen/notify) and sends the
>> queued mail.

> For the record, this is _way_ more robust. It also prevents your
> database from accidentally DoSing your mail server, as it would if
> thousands of mail messages were all triggered in a very short period
> of time.

You need to think about which failure mode you like better. If you have
transactions send mail on their own behalf, you have the problem that a
transaction might send mail and then fail, rolling back and leaving no
trace of itself in the DB ... but that mail went somewhere. If you use
the queue approach, then the mail-sending process will only see queue
entries from committed transactions, and so it will certainly never send
mail that shouldn't have been sent. But you might have problems with
the mail-sending process not actually doing its job, or doing it
multiple times (eg, if it sends mail and then fails to commit removal of
the queue entry, it'd probably reprocess the same entry later). So
neither way is perfect. You pays your money and you takes your choice.

regards, tom lane