Need some advice on appropriate PL strategy...

"Eric D. Nielsen" <nielsene@MIT.EDU> writes:

I'm in the process of implementing a "monitor this" type feature on a
web-application. When something changes on the monitored item an email to the
subscriber is generated. I'd like to do this via triggers instead of
application logic. As far as I can tell pl/pgsql does not include any method
for e-mailing as a function side-effect.

None of the "trusted" languages will allow anything like this for security
reasons. They have to be things that would be safe for a database admin to
allow untrusted users to use. You'll need to use something like PerlU or
PythonU.

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.

--
greg

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/