On Feb 17, 2008 7:47 AM, Jorge Godoy <jgodoy@gmail.com> wrote:
> Em Friday 15 February 2008 12:36:37 Adam Rich escreveu:
> > > I would instead queue messages (or suitable information about them) in
> > > a table, and have a process outside PostgreSQL periodically poll for them
> >
> > Why poll when you can wait?
> >
> > http://www.postgresql.org/docs/8.2/interactive/sql-notify.html
>
> To use cron and prevent system resources being allocated when not needed?
>
> To prevent one other external system to keep a connection open to the
> database?
>
> To guarantee that if the external system crashed somehow during one batch
> execution it would still keep sending emails when it was activated again?
>
> I can see notify being useful to GUI clients that need to update some
> information in near real time. Even for web applications it is easier to
> code a "poll" and a refresh using AJAX to prevent full screen redraws.
>
> I don't see LISTEN/NOTIFY as an useful tool for sending emails, though.
It is not useful as a single tool for the purpose.
It is, however, useful as a component that allows a "polling" system
to be more quickly notified of change without needing to pummel the
DBMS with more queries. Indeed, the use of LISTEN/NOTIFY would mean
that the "polling system" could fairly much ensure that the only time
it submitted queries to the DBMS is when there is actually work to be
done.
Yes, you need to hold a connection open, but that could be *cheaper*
than having a cron job open and close a connection once a minute.
It's fair to say that a cron-based system would need to be designed to
"reset itself" for each iteration. That does not prevent putting the
same design aspects into a LISTEN/NOTIFY-based solution to the
problem, THAT WOULD BE CHEAPER.
But by quibbling over this, I think we're diverging from the Original
Poster's real problem. In that he was considering sending email
direct from a SP, I don't think fine points of optimization were high
in his priorities.
I'd agree that a cron-based system that wakes up once every few
minutes is likely to be a bit simpler to build and deploy than a
"daemon" that uses LISTEN/NOTIFY. That simplicity may make the cron
approach preferable.
--
http://linuxfinances.info/info/linuxdistributions.html
"The definition of insanity is doing the same thing over and over and
expecting different results." -- assortedly attributed to Albert
Einstein, Benjamin Franklin, Rita Mae Brown, and Rudyard Kipling
