Re: Trigger to call an external program - Mailing list pgsql-admin

From Alan Graham
Subject Re: Trigger to call an external program
Date
Msg-id 1070617650.2670.8.camel@bart.graham.fdns.net
Whole thread Raw
In response to Re: Trigger to call an external program  (Peter Childs <blue.dragon@blueyonder.co.uk>)
List pgsql-admin
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.graham@infonetsystems.com.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.graham@infonetsystems.com.au>


pgsql-admin by date:

Previous
From: Jim Cochrane
Date:
Subject: Postrgres data restoration problem
Next
From: Sai Hertz And Control Systems
Date:
Subject: Re: Postrgres data restoration problem