Thread: Need some advice on appropriate PL strategy...

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.  So I need
to choose a different PL.  The function is basically a series of
queries, coupled with a mailing  ( look up details of new event; lookup
subscribers and exclude poster of new event from subscriber list, if
present; email subscribers) -- so, aside from the emailing bit, its
trivial in pl/pgsql.

The web application is in PHP, so using pl/PHP could be nice, but I
haven't found a lot of information on pl/PHP to know its relative
maturity/stableness.  I'm also familiar with Java, Python, C, much less
familiar with Perl.  In order to e-mail will I need a trusted or
untrusted version of the procedural language?

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?

Thank you,
Eric Nielsen

"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