Re: Trigger that spawns forked process

On Tue, 2005-05-10 at 11:11 -0500, Jim C. Nasby wrote:
> Well, LISTEN and NOTIFY are built into PostgreSQL
> (http://www.postgresql.org/docs/8.0/interactive/sql-notify.html). If the
> processes that you're trying to notify of the changes are connected to
> the database then this might be the easiest way to do what you're
> looking for. Setting up some form of replication, such as Slony, also
> comes to mind. But it's impossible to really make a recommendation
> without having a better idea of what you're doing.
>
> BTW, my understanding is that it's pretty easy to write a daemon in
> perl, and there are examples of how to do this floating around.

Yes, I saw the LISTEN/NOTIFY stuff, and it could be interesting. As to
the replication, Slony won't do it for me, as it isn't the database I
want to replicate. Here's a basic description:

I have 4 cluster nodes all running the same content management software
(home grown). When a change request comes in to one of them (update to
an XML document), it submits the new XML doc to the database (which is
the master repository of all content), then performs an XSLT. Upon the
new change, I want the database to propagate the new result of the XSLT
to the other nodes so that they can pre-cache it (to avoid page loading
latency).

I was given an example of how to spawn a forked process with plperlu,
and it looks pretty simple and straightforward and exactly what I want:

CREATE or REPLACE function somefunc() returns void as $$
$SIG{CHLD}='IGNORE';
# the preceding line removes any zombies created.
# Assumes you do not want to handle the return value
#from the child process

unless (defined ($pid=fork)) {
die "cannot fork: $!";
}
unless ($pid) {
$cmd="your command here";
system "$cmd";
if ($? != 0) {
# handle errors here
}
exit;
}
RETURN;
$$ language plperlu;

This seems to be pretty trivial, and near fail-proof to me. my '$cmd'
would then be a script that handles the replication of the cached file
to the nodes (already written and tested). Why is a daemon more robust
than this? (BTW, I ask out of ignorance, not out of arrogance).

Cheers,

Chris
--
Christopher Murtagh
Enterprise Systems Administrator
ISR / Web Service Group
McGill University
Montreal, Quebec
Canada

Tel.: (514) 398-3122
Fax: (514) 398-2017