Re: sending mail from Postgres

----- Original Message -----
From: "Vivek Khera" <vivek(at)khera(dot)org>
To: "PG-General General" <pgsql-general(at)postgresql(dot)org>
Sent: Wednesday, December 28, 2005 11:48 AM
Subject: Re: [GENERAL] sending mail from Postgres

>
> On Dec 28, 2005, at 8:28 AM, Christopher Browne wrote:
>
>> Some separate process (perhaps polling, perhaps using NOTIFY/LISTEN)
>> would then grab messages from the table and submit them to [whatever
>> is the communications layer].
>>
>> That is a clean sort of design for that sort of thing.
>
> This is precisely how we build things. Throw in some good locking
> mechanisms and you can scale this to incredible levels.
>
Here is a general question relating to this. The problem involves due
diligence related to environmental protection. Consider a distributed
application to support this. You have a facility with an environmental
monitoring program. Samples are taken from soil, water and air and sent
off-site for analysis. Each sample, when analyzed, results in a report sent
to the facility's management for their records. However, if the
concentration of some contaminant in a sample is above some threshold, a
second report, containing the complete analysis results for the sample, is
generated and sent to one or more people, both inside and outside the
organisation (e.g. engineers within the organization responsible for fixing
problems with the facility and engineers at relevant regulatory agencies).
One objective is to automate as much of the data management as possible and
to ensure that if a problem arises everyone who needs to know about it is
notified. The process has to be auditable, so that information about when
each step in the process starts is stored in the database, as well as
information about when messages are acknowledged (again automated - so when
an engineer opens a message about a problem, an acknowledgement is sent to
the database without his intervention).

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

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

Cheers,

Ted
R.E. (Ted) Byers, Ph.D., Ed.D.
R & D Decision Support Software
http://www.randddecisionsupportsolutions.com/