Re: Trigger to call an external program

I'll try the fix and compile of pgplsh, as suggested by Stephan. (thanks
:-) I'd assumed that the errors I was seeing were indicative of much
larger compatibility problems. Just shows what "Assume" does :-)

As you say, using trigger worries me, because of rollbacks. I'd thought
of using NOTIFY, but the limited docs I could find suggested that it
wouldn't be fine grained enough to replicate every transaction. Also
(and this was more of an issue), I couldn't work out how to LISTEN in
Python. Could you point me to some docos or examples of an external
python script LISTENing for a NOTIFY?

Thanks for your help

Alan Graham

On Thu, 2003-12-04 at 23:43, Peter Childs wrote:
> 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
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
--
Alan Graham <alan(dot)graham(at)infonetsystems(dot)com(dot)au>