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. _______________________________
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
