>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
