Re: Plperlu and sending emails, is it safe?

"Philippe Lang" <philippe(dot)lang(at)attiksystem(dot)ch> writes:
> I need to send emails from Postgresql triggers.

> I was able to do it with a 'plperlu' function, that calls the 'system'
> perl function, that itself calls the 'mail' shell function.

> Is is safe to do things like this? Is there a possible concurrent access
> that could arise?

The real issue here is transactional semantics. What happens if the
transaction that fired the trigger rolls back due to a later error?
The transaction effectively never happened, but you can't unsend the mail.

> Another solution would be to use cron every 5 minutes, and read the
> content of a table.

This would probably be better because the cron job could only see the
results of committed transactions. The failure mode in this case is
that the same mail could be sent more than once (if the cron job fails
between sending the mail and committing its update that deletes the
entry in the pending-mails table). But you'd not be wondering why you
got mail that seems not to be related to anything visible in the
database.

As noted elsewhere, you could use NOTIFY of a persistent daemon process
instead of a cron job. This might be a win if you would like the mail
sent more quickly than ~5 minutes. The point is to put the actual
sending in a separate transaction ...

regards, tom lane