Thread: Trigger to call an external program
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. 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 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>
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. > > 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 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. It takes a little bit of work to get pgplsh to compile for my devel system (notably that I needed to make sure the server includes were in the -I path and had to make a few more changes noted below). I removed the if (DebugLvl > 1) lines and needed to change elog(DEBUG, lines to one of the new DEBUG levels (I chose DEBUG2). After that it seemed to compile.
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
>
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>
On Thursday 04 December 2003 16:36, Stephan Szabo wrote: > > 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. > > It takes a little bit of work to get pgplsh to compile for my devel > system (notably that I needed to make sure the server includes were > in the -I path and had to make a few more changes noted below). > > I removed the if (DebugLvl > 1) lines and needed to change > elog(DEBUG, lines to one of the new DEBUG levels (I chose DEBUG2). > After that it seemed to compile. I'll try to make new "releases" over the weekend that compile properly for the various PostgreSQL versions that are around.
Thanks for this Peter. I got it working by putting in the changes
suggested by Stephan and adding errno.h
However, I can't get any triggers to work. I'm new to postgresql, and
to triggers, so it's got to be something I'm doing wrong.
The test.sql script only half works. The behaviour is the same on the
7.2.1 system, and on the 7.3.4 systems that I've hacked, so I don't
*think* that I've introduced a problem.
Using a pgplsh function works. The test script first creates a function
shtest then runs it. This works. The second part of the script,
creating the function shtrigger, works. The create of the trigger
testtrigger works, ie, I can see the created trigger in the table
pg_trigger. But the 3 inserts insert the data correctly, but there is
no sign that the trigger is being fired off. I've tried various
functions to be fired as triggers, the simplest was
CREATE FUNCTION alantrigger() RETURNS opaque AS
'#!/bin/bash
echo "Hello world"
exit 0
' LANGUAGE 'plsh';
but none of them seem to have any effect.
The output of test.sql (on the 7.3.4 system) is as follows:
bash-2.05a$ psql -f test.sql
DROP FUNCTION
CREATE FUNCTION
shtest
-------------------
One: foo Two: bar
(1 row)
psql:test.sql:12: ERROR: shtest: this is an error
DROP FUNCTION
CREATE FUNCTION
DROP TABLE
CREATE TABLE
psql:test.sql:29: NOTICE: CreateTrigger: changing return type of
function shtrigger() from OPAQUE to TRIGGER
CREATE TRIGGER
INSERT 56863 1
INSERT 56864 1
INSERT 56865 1
cat: /home/e-smith/files/users/infonet/voodoo-pgplsh-test: No such file
or directory
rm: cannot remove
`/home/e-smith/files/users/infonet/voodoo-pgplsh-test': No such file or
directory
I made 2 minor changes to the script. I put a CASCADE into the drop
function so it would drop the trigger as well,, and removed the drop
trigger statement.
Any thoughts as to how I can debug this further are greatly
appreciated. I'm not sure what to try next.
Thanks
Alan Graham
On Sat, 2003-12-06 at 02:51, Peter Eisentraut wrote:
> On Thursday 04 December 2003 16:36, Stephan Szabo wrote:
> > > 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.
> >
> > It takes a little bit of work to get pgplsh to compile for my devel
> > system (notably that I needed to make sure the server includes were
> > in the -I path and had to make a few more changes noted below).
> >
> > I removed the if (DebugLvl > 1) lines and needed to change
> > elog(DEBUG, lines to one of the new DEBUG levels (I chose DEBUG2).
> > After that it seemed to compile.
>
> I'll try to make new "releases" over the weekend that compile properly
> for the various PostgreSQL versions that are around.