Date: Tue, 17 May 2005 13:01:18 -0500
From: Jay Parker <jbparker@ualr.edu>
Subject: triggering an external action
To: pgsql-sql@postgresql.org
Message-id: <428A316E.7090401@ualr.edu>
MIME-version: 1.0
Content-type: text/plain; charset=ISO-8859-1; format=flowed
Content-transfer-encoding: 7BIT
User-Agent: Mozilla Thunderbird 1.0 (X11/20041206)

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

-- 
Jay Parker  -  UALR Computing Services  -  Networks Project Manager
jbparker@ualr.edu  -  http://www.ualr.edu/jbparker  -  501-569-3345
But I have promises to keep, And miles to go before I sleep. -Frost