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

Tue, 20 May 2003 11:30:02 +0200
Kaare Rasmussen <kar@kakidata.dk>
Calling external program from trigger
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

Bye
 Shridhar

--
MIPS:	Meaningless Indicator of Processor Speed


Tue, 20 May 2003 15:41:16 +0530
"Shridhar Daithankar" <shridhar_daithankar@persistent.co.in>
Re: Calling external program from trigger
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

Tue, 20 May 2003 11:03:11 -0400
Tom Lane <tgl@sss.pgh.pa.us>
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 #


Tue, 20 May 2003 11:25:22 -0400
Jan Wieck <JanWieck@Yahoo.com>
Re: Calling external program from trigger
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

Tue, 20 May 2003 17:34:08 +0200
Kaare Rasmussen <kar@kakidata.dk>
Re: Calling external program from trigger
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

Tue, 20 May 2003 21:29:49 +0200
Kaare Rasmussen <kar@kakidata.dk>
Re: Calling external program from trigger