Re: mail alert - Mailing list pgsql-sql

tim@tim-landscheidt.de (Tim Landscheidt) writes:
> Alvaro Herrera <alvherre@commandprompt.com> wrote:
>
>>> > It's on Windows
>
>>> I'd go with notify and a listener written in C using c-client to send
>>> emails, but only because I've used those before.
>
>> I wouldn't write it in C but rather Perl or Python, but whatever suits
>> your fancy should work (Visual Basic anyone?).  The advantages to using
>> a listener program instead of doing it in a trigger or something like
>> that are:
>
>> - transaction semantics are kept; you don't send an email only to find
>> out your transaction has been rolled back for whatever reason, and then
>> send a second email when the transaction is replayed
>
>> - you don't block the database system just because your mail server is
>> down
>
>> - the email can be sent on whatever schedule fits the listener program
>
>> - the listener client can run elsewhere, not only in the database server
>
>> - any further external processing can take place at that time, without
>> bothering the database server
>
>> - other stuff I don't recall ATM
>
> The main disadvantage in using a listener is that it is your
> responsibility to make sure that the listener is listening
> 24/7 - from before the database accepts other connections,
> through network failures, bugs, etc. - otherwise notifica-
> tions will be lost. Therefore I find it much more reliable
> (and easier to program) to copy the relevant data to a table
> "mailqueue" (or whatever) and then process that queue every
> other minute.

Actually, I don't think there's any real disagreement here...
- The *important* bit is to make sure that the data required to  generate the email is queued in the database.
- Whether you poll or use notify/listen is *way* less important.

You could implement the "listener process" a number of ways:
 - It could be a "cron" that wakes up every so often   to do whatever work is outstanding
 - It could be a "polling daemon" that sleeps for a while between   iterations.
   That seems a little nicer than the "cron" approach in that it   eliminates a troublesome scenario, namely the case
wherethere's a   lot of work to do (flooded queue?)  so that processing takes longer   than the polling interval,
leadingto the risk that a second "cron"   starts up while the previous one is still working.
 
 - It could be a "listening daemon" that listens for notifications to   indicate that work is outstanding
   That is a little better than the "polling daemon" in that it doesn't   need to wait the full polling period to start
processingnew work.
 

Any of those three approaches are quite viable, as long as you're
careful to cover scenarios like:- daemon falling over- accidentally starting multiple "queue processors"
-- 
output = reverse("ofni.sailifa.ac" "@" "enworbbc")
Christopher Browne
"Bother,"  said Pooh,  "Eeyore, ready  two photon  torpedoes  and lock
phasers on the Heffalump, Piglet, meet me in transporter room three"