>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?)
This sounds like a job for LISTEN/NOTIFY: http://www.postgresql.org/docs/8.0/static/sql-listen.html http://www.postgresql.org/docs/8.0/static/sql-notify.html //Magnus ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
