Re: Need psql send email

Hi all;

A couple points here.

First, you probably don't want to send email directly from a database
function. This gives significant problems for which there is no good
solution. Consider:

1) You sent your email and now the transaction rolls back. You *cannot*
roll back the sent email.

2) Your email fails to send. Do you abort the transaction?

IMO it is always better to send email from a second process that can be
notified on db commit. This avoids these issues and kicks them to a
post-transaction handler.

As luck would have it, I recently set a project up on Google Code to help
address this (and other application integration) issues. See
http://code.google.com/p/pg-message-queue/

There isn't a lot of overlap with something like pgq. This is
listen/notify/queue tables based. May not ever be big and professional but
it should work once the bugs are ironed out. Even before then it may give
a good idea of how to implement a notification-based queue on PostgreSQL.

The idea here is that you can essentially send a message to a channel on a
db event (say, from a trigger) and then have another app that either
periodically checks the queue (say, from a cron job) or listens on a
channel for notifications.

The whole thing was confirmed working before I made some changes. If folks
are interested in helping I am sure it will be well tested and working in
no time. Please read the docs first though. I wouldn't say it is
production-ready yet, but it may provide an overview of how to go about
implementing something like this in production.

Also for more info on how to do this with a LISTEN/NOTIFY approach outside
of the above, see
http://ledgersmbdev.blogspot.com/2012/09/objectrelational-interlude-messaging-in.html

In general I think mixing transactional and non-transactional side-effects
is just asking for trouble. Don't do it any more than you have to.

Best Wishes,
Chris Travers