From: Christopher Browne <cbbrowne@ca.afilias.info>
Subject: Re: mail alert
Date: Fri, 14 Aug 2009 11:18:21 -0400
Organization: Hub.Org Networking Services
Lines: 74
Message-ID: <87ab22qv9e.fsf@dba2.int.libertyrms.com>
References: 
 <E30C7040DE22624185BAD4093190B54437BE5DB49C@EX2007-MBX-2.uz.kuleuven.ac.be>
	<EC8A0653E149420D898593D05B1E07FF@ramasubramanian>
	<E30C7040DE22624185BAD4093190B54437BE5DB4A9@EX2007-MBX-2.uz.kuleuven.ac.be>
	<h6106l$h8t$2@reversiblemaps.ath.cx>
	<20090813215420.GU5909@alvh.no-ip.org>
	<m3bpmjfj3s.fsf@passepartout.tim-landscheidt.de>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/23.1 (gnu/linux)
Cancel-Lock: sha1:nv1bmq5sPE/HjV2lwY0H8m7gaGg=
To: pgsql-sql@postgresql.org

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 where there's a
    lot of work to do (flooded queue?)  so that processing takes longer
    than the polling interval, leading to 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 processing new 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"