Skip site navigation (1) Skip section navigation (2)

Re: sending mail from Postgres

From: "Ted Byers" <r(dot)ted(dot)byers(at)rogers(dot)com>
To: "PG-General General" <pgsql-general(at)postgresql(dot)org>
Subject: Re: sending mail from Postgres
Date: 2005-12-28 17:49:10
Message-ID: 033a01c60bd7$021829d0$6401a8c0@RnDworkstation (view raw, whole thread or download thread mbox)
Lists: pgsql-general
----- 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?


R.E. (Ted) Byers, Ph.D., Ed.D.
R & D Decision Support Software 

In response to


pgsql-general by date

Next:From: Randal L. SchwartzDate: 2005-12-28 17:59:43
Subject: Re: sending mail from Postgres
Previous:From: Jim C. NasbyDate: 2005-12-28 17:48:24
Subject: Re: just an inconvenience, or.... : failed queries don't appear in the log anymore since v8.?.?

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group