Re: Running OS-level programs from Postgres?

Sean(dot)Murphy(at)equipoint(dot)com (Sean Murphy) writes:
> My present need is for email notification; if there's a pg function or
> module that would handle this (I haven't turned up anything in my
> searches, but maybe I'm using the wrong search terms in the wrong
> places) I'd be OK for now, but I'd rather have the flexibility of
> starting whatever process a given circumstance calls for.

There are ways of doing this via untrusted stored function languages,
but this approach tends to be discouraged.

Consider: If you submit 200 of these requests, you may spawn your
mailer system 200 times, which may be a spectacularly inefficient
thing to do. Indeed, spawning it 200 times in a second might trigger
Negative Reactions in system components. (Consider: What if your
system tried delivering 200 pieces of mail concurrently, and spawned,
as a side-effect, 200 copies of SpamAssassin?)

The better answer tends to be to try to decouple this.

- Write the data that you want sent out into a row in a table that
implements a Message Queue.

- Perhaps submit a NOTIFY request, if you want things to kick off at
once.

- Have some other, separate process, that LISTENs for notifications
(or which simply wakes up once in a while). This process goes
through the Message Queue, doing some work on each item to send the
message on towards its destination.

This way there is only ONE process that wakes up and processes the
work queue. It might be a program that's mostly outside PostgreSQL...
--
"cbbrowne","@","cbbrowne.com"
http://linuxdatabases.info/info/rdbms.html
"It is not enough to succeed, others must fail." -- Gore Vidal