Re: sending mail from Postgres

>>>>> "Ted" == Ted Byers <r(dot)ted(dot)byers(at)rogers(dot)com> writes:

Ted> Here is a general question relating to this. The problem involves due
Ted> diligence related to environmental protection. Consider a distributed
Ted> application to support this. You have a facility with an
Ted> environmental monitoring program. Samples are taken from soil, water
Ted> and air and sent off-site for analysis. Each sample, when analyzed,
Ted> results in a report sent to the facility's management for their
Ted> records. However, if the concentration of some contaminant in a
Ted> sample is above some threshold, a second report, containing the
Ted> complete analysis results for the sample, is generated and sent to one
Ted> or more people, both inside and outside the organisation
Ted> (e.g. engineers within the organization responsible for fixing
Ted> problems with the facility and engineers at relevant regulatory
Ted> agencies). One objective is to automate as much of the data management
Ted> as possible and to ensure that if a problem arises everyone who needs
Ted> to know about it is notified. The process has to be auditable, so
Ted> that information about when each step in the process starts is stored
Ted> in the database, as well as information about when messages are
Ted> acknowledged (again automated - so when an engineer opens a message
Ted> about a problem, an acknowledgement is sent to the database without
Ted> his intervention).

Ted> I suppose email might work as a means of sending messages, but I was
Ted> thinking of Sun's JMS instead, working with triggers. I could then
Ted> create my own thin client to display the reports, perhaps sorting them
Ted> according to user specified criteria. I can see how to do it within
Ted> the web tier, or within the client tier (within the labs doing the
Ted> analyses). The thing is, of the designs I have considered, the one
Ted> involving triggers with JMS on the main supporting website (with a
Ted> database back end) is the simplest in terms of deployment, since all
Ted> interested parties could interact with the application through the
Ted> internet using a very thin client (perhaps even with an applet within
Ted> a web page) and I would not need to worry about deploying software to
Ted> all relevant people/sites.

Ted> If you faced this problem, what would you do, and why?

As already proposed, I'd have a trigger noticing the exceptional
condition post a record to an audit log table (and use NOTIFY). Then
I'd have a notification process manage watching that table, and send
the notices as needed.

That's the right level for this. Database triggers should be about
adding rows, deleting rows, and modifying values in rows to manage
integrity. It's the middleware that's responsible for repackaging
that or acting on changed data.

--
Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095
<merlyn(at)stonehenge(dot)com> <URL:http://www.stonehenge.com/merlyn/>
Perl/Unix/security consulting, Technical writing, Comedy, etc. etc.
See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!