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

#1

Quote:

From: Greg Stark <gsstark (AT) mit (DOT) edu
Subject: Re: Need some advice on appropriate PL strategy...
Message-ID: <874ql033q0.fsf (AT) stark (DOT) xeocode.com

"Eric D. Nielsen" <nielsene (AT) MIT (DOT) 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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

#2

On Tue, Oct 12, 2004 at 12:20:46PM -0400, Eric D Nielsen wrote:

Quote:

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/

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

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

comp.databases.postgresql.general comp.databases.postgresql.general

Re: Need some advice on appropriate PL strategy... ["solved/thanks"] - 10-12-2004 , 11:20 AM

Re: Need some advice on appropriate PL strategy... ["solved/thanks"] - 10-14-2004 , 12:20 PM