Trigger to call an external program

[Alan Graham]

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

[Stephan Szabo]

Post by Alan Graham
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.


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Peter Eisentraut]

Post by Stephan Szabo

Post by Alan Graham
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.


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

[Alan Graham]

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

Post by Peter Eisentraut

Post by Stephan Szabo

Post by Alan Graham
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.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Peter Childs]

Post by Alan Graham
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.

Post by Alan Graham
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.

Post by Alan Graham
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
--
---------------------------(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]

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

Post by Peter Childs

Post by Alan Graham
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.

Post by Alan Graham
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.

Post by Alan Graham
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
--
---------------------------(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