Re: Trigger to call an external program

On Thu, 4 Dec 2003, Alan Graham wrote:

> I'm doing a low cost, low performance roll your own replication project,
> between various offices around Australia. The replication is
> asynchronous, and peer to peer. I've looked at the various replication
> projects, and for one reason or another, they're too hard.

Agreed.

>
> My design uses jabber as middleware, using python scripts to do the hard
> work. However, it depends on triggers calling my python scripts to
> work. I can't find a way to call an external script from a trigger.
> I've searched the archives, and found a few other people asking the same
> question, but no answers (apart from the odd sanctimonious "well you
> shouldn't be doing that..." :-).

I've just done that. The problem is that the trigger gets fired
even if the query then gets rolled back. So its best to use notify. I
orignally wrote the trigger in python but after finding a bug in the
python scripting for postgres 7.3 I gave up and rewrote in in C its faster
anyway :) My demon script is not bug free however....I want it to inform
users about database changes not for replication however.... so it uses
mu-conference and broardcasts the queries to anyone listening.

>
> I thought I was onto a winner with pgplsh, but I can't get it to compile
> with 7.3.4, although it compiled beautifully with 7.2.1.
>
> Using python as a trigger language also seems to be a non-starter,
> because the restricted python environment isn't yet working.
>
> So... Is there an easy way to call a script from a trigger?
>
> Thanks
>
> Alan Graham
>
> --
> Alan Graham <alan(dot)graham(at)infonetsystems(dot)com(dot)au>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>