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(at)Yahoo(dot)com #