Thread: Re: Need some advice on appropriate PL strategy... ["solved/thanks"]

> From: Greg Stark <gsstark@mit.edu>
> Subject: Re: Need some advice on appropriate PL strategy...
> Message-ID: <874ql033q0.fsf@stark.xeocode.com>
>
> "Eric D. Nielsen" <nielsene@MIT.EDU> writes:
> > I guess I could alternatively just code up a simple mail function in
> another PL
> > and then call that function from pl/pgsql.  Is there any merit to this
> approach
> > over the "whole-trigger" in another PL method?
>
> Well depending on your application this may be a reasonable approach.
> However
> you should at least think carefully before taking this route. It means the
> email processing is put into the critical path of performing the original
> update.
>
> I would suggest you consider another model, where you have a second process
> that connects to the database and checks for updates. It can either stay
> connected all the time and the trigger can use NOTIFY to wake it up. Or it
> can
> just check periodically. This has the advantage that you can write in any
> language that has a postgres driver, including PHP.
>
> It also means you can perform your database updates without having them
> depend
> on some large external system. This is a big advantage. It means when the
> mail
> system's borked you can keep your web application running and have it catch
> up
> when things are fixed. And it means when things are slow or erroneous you
> have
> one fewer moving parts to confuse you when debugging.

Hmm, very good points.  Thank you.  I was hoping for a get/easy solution, but
those never pan out :)  Your suggestion is also much more flexible -- digesting
or other similar aggregation of multiple events to single emails is much easier
to implement in that scenario.

Thanks again.

Eric

On Tue, Oct 12, 2004 at 12:20:46PM -0400, Eric D Nielsen wrote:
>
> I was hoping for a get/easy solution, but those never pan out :)

Here's a trivial Python program that handles NOTIFY events; it
couldn't get much easier:

#!/usr/local/bin/python

import psycopg
import select

conn = psycopg.connect('dbname=test')
conn.autocommit(1)

curs = conn.cursor()
curs.execute('LISTEN alert')

fd = curs.fileno()

while True:
    select.select([fd], [], [], None)
    curs.execute('SELECT 1')
    print curs.notifies()

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/