On Feb 17, 2008 7:47 AM, Jorge Godoy <jgodoy@xxxxxxxxx> 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 ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
