Thread: Calling external program from trigger

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. 

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.

-- 
Kaare Rasmussen            --Linux, spil,--        Tlf:        3816 2582
Kaki Data                tshirts, merchandize      Fax:        3816 2501
Howitzvej 75               Åben 12.00-18.00        Email: kar@kakidata.dk
2000 Frederiksberg        Lørdag 12.00-16.00       Web:      www.suse.dk

On 20 May 2003 at 11:30, Kaare Rasmussen wrote:

> Hi
> 
> I would like to notify a server whenever a certain table is updated.

There were some replies on this earlier, but I deleted most of them in morning 
session.

You can write a trigger in C and signal a PID, where you can get the PID from a 
a location that your server and C trigger knows about.. Maintain in a database 
config table as a solution.

HTH

ByeShridhar

--
MIPS:    Meaningless Indicator of Processor Speed

Kaare Rasmussen <kar@kakidata.dk> writes:
> I would like to notify a server whenever a certain table is updated.

What do you mean by "notify" exactly?

Perhaps you could have a background job that remains always connected to
the database, but sleeps waiting for a NOTIFY to arrive.  A rule like
"ON UPDATE TO foo DO NOTIFY foo_changed" would serve to awaken it.
        regards, tom lane

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 #

Hi Jan and Tom

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

Thanks for the pointers. I will read up on notify. It seems to do the job.

-- 
Kaare Rasmussen            --Linux, spil,--        Tlf:        3816 2582
Kaki Data                tshirts, merchandize      Fax:        3816 2501
Howitzvej 75               Åben 12.00-18.00        Email: kar@kakidata.dk
2000 Frederiksberg        Lørdag 12.00-16.00       Web:      www.suse.dk

HI

This may be more directed to Bruce or any other Perl maintainer.

Looking at Pgperl it seems very simple:

while (1) {   $conn->consumeInput;   ($table, $pid) = $conn->notifies;   last if $pid;
}

consumeInput will wait until something happens and we check if it is a 
notification.

But can the same be doen with DBD::Pg?

-- 
Kaare Rasmussen            --Linux, spil,--        Tlf:        3816 2582
Kaki Data                tshirts, merchandize      Fax:        3816 2501
Howitzvej 75               Åben 12.00-18.00        Email: kar@kakidata.dk
2000 Frederiksberg        Lørdag 12.00-16.00       Web:      www.suse.dk