Message-ID: <428A32FF.7030109@PresiNET.com>
Date: Tue, 17 May 2005 11:07:59 -0700
From: Bricklen Anderson <BAnderson@PresiNET.com>
User-Agent: Debian Thunderbird 1.0.2 (X11/20050331)
MIME-Version: 1.0
To: Jay Parker <jbparker@ualr.edu>
Cc: pgsql-sql@postgresql.org
Subject: Re: triggering an external action
References: <428A316E.7090401@ualr.edu>
In-Reply-To: <428A316E.7090401@ualr.edu>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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.
_______________________________