Re: Running OS-level programs from Postgres?

Maybe this is a well duh kind of question, or maybe there's no
straightforward way to do it, but is there any way that I could have a
pg function initiate a process on the host system?

Specifically I'd like to script an email to send off on an insert
trigger, but the ability to initiate system processes in general seems
like it would come in handy.

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.

Thanks.

Sean Murphy wrote:
> Maybe this is a well duh kind of question, or maybe there's no
> straightforward way to do it, but is there any way that I could have a
> pg function initiate a process on the host system?

Yeah you can use any of the untrusted pl languages for that.

>
> Specifically I'd like to script an email to send off on an insert
> trigger, but the ability to initiate system processes in general seems
> like it would come in handy.
>
> 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.
>
> Thanks.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

If you can use tcl based pl function, the this might help you here -->
http://sourceforge.net/projects/pgmail/

--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)

On 6/20/07, Joshua D. Drake <jd(at)commandprompt(dot)com> wrote:
>
> Sean Murphy wrote:
> > Maybe this is a well duh kind of question, or maybe there's no
> > straightforward way to do it, but is there any way that I could have a
> > pg function initiate a process on the host system?
>
> Yeah you can use any of the untrusted pl languages for that.
>
> >
> > Specifically I'd like to script an email to send off on an insert
> > trigger, but the ability to initiate system processes in general seems
> > like it would come in handy.
> >
> > 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.
> >
> > Thanks.
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: Don't 'kill -9' the postmaster
> >
>
>
> --
>
> === The PostgreSQL Company: Command Prompt, Inc. ===
> Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
> Providing the most comprehensive PostgreSQL solutions since 1997
> http://www.commandprompt.com/
>
> Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
> PostgreSQL Replication: http://www.commandprompt.com/products/
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org/
>

On Jun 20, 2007, at 12:07 PM, Sean Murphy wrote:

> Maybe this is a well duh kind of question, or maybe there's no
> straightforward way to do it, but is there any way that I could have a
> pg function initiate a process on the host system?

You can use pl/perlu or any of the other untrusted languages for
that.

>
> Specifically I'd like to script an email to send off on an insert
> trigger, but the ability to initiate system processes in general seems
> like it would come in handy.
>
> 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.

Another approach, and one that can be more robust in the case
of external failures, is to have the trigger put the message it wants
to send into a queue table and have an external process that
monitors the table (via polling or listen/notify) and sends the
queued mail.

Cheers,
Steve

> Maybe this is a well duh kind of question, or maybe there's no
> straightforward way to do it, but is there any way that I could have a
> pg function initiate a process on the host system?
>
> Specifically I'd like to script an email to send off on an insert
> trigger, but the ability to initiate system processes in general seems
> like it would come in handy.
>
> 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.

I've never done it, and while you can do it with the various pl languages,
you might also look into the notice/listen stuff.

That way your app doesn't have to wait for the process to finish, but can
send a notice which another server side app can listen for and do whatever
it needs to do.

http://www.postgresql.org/docs/current/static/sql-notify.html
http://www.postgresql.org/docs/current/static/sql-listen.html

On Wed, Jun 20, 2007 at 12:55:23PM -0700, Steve Atkins wrote:
>
> Another approach, and one that can be more robust in the case
> of external failures, is to have the trigger put the message it wants
> to send into a queue table and have an external process that
> monitors the table (via polling or listen/notify) and sends the
> queued mail.

For the record, this is _way_ more robust. It also prevents your
database from accidentally DoSing your mail server, as it would if
thousands of mail messages were all triggered in a very short period
of time.

A

--
Andrew Sullivan | ajs(at)crankycanuck(dot)ca
The very definition of "news" is "something that hardly ever happens."
--Bruce Schneier

Andrew Sullivan <ajs(at)crankycanuck(dot)ca> writes:
> On Wed, Jun 20, 2007 at 12:55:23PM -0700, Steve Atkins wrote:
>> Another approach, and one that can be more robust in the case
>> of external failures, is to have the trigger put the message it wants
>> to send into a queue table and have an external process that
>> monitors the table (via polling or listen/notify) and sends the
>> queued mail.

> For the record, this is _way_ more robust. It also prevents your
> database from accidentally DoSing your mail server, as it would if
> thousands of mail messages were all triggered in a very short period
> of time.

You need to think about which failure mode you like better. If you have
transactions send mail on their own behalf, you have the problem that a
transaction might send mail and then fail, rolling back and leaving no
trace of itself in the DB ... but that mail went somewhere. If you use
the queue approach, then the mail-sending process will only see queue
entries from committed transactions, and so it will certainly never send
mail that shouldn't have been sent. But you might have problems with
the mail-sending process not actually doing its job, or doing it
multiple times (eg, if it sends mail and then fails to commit removal of
the queue entry, it'd probably reprocess the same entry later). So
neither way is perfect. You pays your money and you takes your choice.

regards, tom lane

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