Re: triggering an external action

Jay Parker wrote:
> I am trying to find the best way for a database trigger to signal a 
> client process to take an action.
> 
> Specifically, I am working on the classic problem of creating and 
> modifying system accounts based on the updates to a "person registry" 
> database.
> 
> The basic model I'm working with has triggers on my tables of interest 
> that stick a person's unique ID into a "todo queue" table whenever 
> modifications are made.  The queue is periodically polled by a script 
> which processes and deletes each "todo" record.  The polling script goes 
> to sleep for gradually increasing periods of time whenever it polls the 
> queue and finds it empty.
> 
> What I want is a trigger on the "todo" table that will "kick" my 
> processing script to make it wake up and process the queue immediately. 
>  In an Oracle environment, I think I could use a database pipe to 
> achieve more or less the behavior I'm looking for, but I can't find 
> anything that does what I want in pgsql at the database level.
> 
> I could write a trigger in C or Perl or something that would do 
> something at the OS level, like send a signal, but when I go down that 
> path I start having to deal with unix issues like having to elevate to 
> root privs to send a signal to a process that isn't running as the pgsql 
> user, etc.  It is doable but gets complex quickly.
> 
> Is there anything I'm missing at the database level that would help me 
> process updates in realtime?  (And if not, which of the other mailing 
> lists would be the most appropriate place for me to discuss the best way 
> to implement an OS-level solution?)
> 
> Thanks,
> 
> -jbp
> 

How about LISTEN and NOTIFY, would they work for this?

-- 
_______________________________

This e-mail may be privileged and/or confidential, and the sender does
not waive any related rights and obligations. Any distribution, use or
copying of this e-mail or the information it contains by other than an
intended recipient is unauthorized. If you received this e-mail in
error, please advise me (by return e-mail or otherwise) immediately.
_______________________________