From: Christopher Browne <cbbrowne@ca.afilias.info>
To: pgsql-sql@postgresql.org
Subject: Re: mail alert
Date: Fri, 14 Aug 2009 11:18:21 -0400
Message-ID: <87ab22qv9e.fsf@dba2.int.libertyrms.com> (raw)
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>
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"
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: cbbrowne@ca.afilias.info
Subject: Re: mail alert
In-Reply-To: <87ab22qv9e.fsf@dba2.int.libertyrms.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox