Re: triggering an external action

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