Обсуждение: 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)
--
Shoaib Mir
EnterpriseDB ( www.enterprisedb.com)
On 6/20/07, Joshua D. Drake <jd@commandprompt.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@crankycanuck.ca The very definition of "news" is "something that hardly ever happens." --Bruce Schneier
Andrew Sullivan <ajs@crankycanuck.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.Murphy@equipoint.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