Re: Calling external program from trigger

Kaare Rasmussen wrote:
> Hi
> 
> I would like to notify a server whenever a certain table is updated.
> 
> I wonder if this hasn't been solved before. I looked at contrib and Gborg with 
> no success. Maybe it could be a function that would be called with parameters 
> like host, port and message.
> 
> I wouldn't like to use untrusted Perl for just this purpose. 

You can use a PL/pgSQL (or any other trusted language) trigger to do a
NOTIFY and have an external program (on the server) LISTENing for that.

The big advantage is that the notification is held back until your
transaction COMMITs.

See

http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=sql-listen.html

http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=sql-notify.html

How listen is actually implemented in the client depends on your
interface. In Tcl for example you register a callback which will then be
called through the regular Tcl event loop (update or vwait).


Jan


> 
> Currently I use cron but there are at least two disadvantages:
> 
> 1. There can be a delay up to 59 seconds. And more if the cron job is set not 
> to run every minute.
> 2. The server has to poll often (in my case every minute), leading to 
> unnecessary database and bandwidth usage.
> 
> These disadvantages are especially annoying when the job is invoked only once 
> in a while, but the user expects to see the result asap.
> 



-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #