Re: How to send an email when data is inserted into a table

Nicholas Allen <Allen(dot)Nicholas(at)lycos(dot)co(dot)uk> writes:

> Hi,
>
> I have a table which stores bugs which can be submitted by users of
> our client program. I would like a way that when a new bug is
> inserted an email is sent to the developers informing them of this.
>
> I tried using a python trigger function to execute sendmail and pipe
> SMTP headers to it but the problem seems to be that the python
> functions are interpretted in a restrictive environment and don't
> allow access to local resources (files /processes etc). When I
> execute the python function outside of postgres it works fine but
> not when used as a trigger functions.
>
> I know I can do it by writing a C function but I would rather not
> use C if possible in the case. Python would be easier to maintain.
>
> Any ideas? Do I have to use C or is there a way to do this from
> pyhton?
>
> Thanks in advance for any help,
>
> Nicholas Allen.

I do something similar, but I do it *outside* of PostgreSQL. I simply
have a Python script that is started from cron. The fact of the
matter is that you probably don't want to muck around with tricky
Python (or C) trigger functions when you can write a simple script and
launch it from cron.

Not to mention the fact that with a script launched from cron you can
let PostgreSQL's transactions work for you instead of against you.
What happens to your Python trigger function if your system is unable
to send mail, for example? Does the transaction get rolled back
possibly losing you important trouble ticket information? With an
external script these sorts of issues become easy to work with. Your
trouble ticket inserts don't have to worry about sending mail. Your
mailer script worries about that. Simply open a transaction, send
your mail, and if you get an error you roll the transaction back and
try to tell someone about it.

Jason