Thread: making a trigger to a system call to a shell script

Hi,

I have the short term problem of needing to know quite fast when someone updates or inserts a column in a table I have.
It'ssomething I hope to automate, but for now I've been looking around for a way to trigger a shell script to send me
anemail whenever this table is added to.  

I looked around on archives of over a year ago on this and other psql lists, and it all seemed to point to creating a
functionin C that is then added as a trigger to postgres. It seems strange to me that this little feature is not there,
butI don't mind coding in C, so I thought I'd give it a go.  

I even found a great reference to this: Bruce Momjan's book, specifically, the little section on "extending postgresql
inC". 

I followed this, created a little function that takes a char and returns the usual int, then I downloaded all of the
newestversion of postgresql, in response to http://www.ca.postgresql.org/docs/aw_pgsql_book/node170.html - and finally
issuedsome dubious compilation commands to make the dynamically linked c file (is this too much work for a seemingly
simpletask like this??), based on the headers I'd just downloaded : 

gcc -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -fpic -I/opt/downloads/postgresql-7.2.1/src/interfaces/libpq
-I/opt/downloads/postgresql-7.2.1/src/include/ -c -o alertme.o alertme.c 

and now I've tried the following command as user postgres, in psql:

create function alertme(char) returns int as '/home/ale/c/alertme.so' language 'C';

and it says "stat failed on file '/home/ale/play/alertme.so': Permission denied"

Permission? here are the file in question's permissions:
-rwxrwxrwx

That's more than's even safe... What am I doing wrong??

Thanks,

Ale


--
Alejandro Fernandez
Electronic Group Interactive
--+34-65-232-8086--

On Thu, 2 May 2002, Alejandro Fernandez wrote:

> and now I've tried the following command as user postgres, in psql:
>
> create function alertme(char) returns int as '/home/ale/c/alertme.so' language 'C';
>
> and it says "stat failed on file '/home/ale/play/alertme.so': Permission denied"
>
> Permission? here are the file in question's permissions:
> -rwxrwxrwx
>
> That's more than's even safe... What am I doing wrong??

What about the directories it's in?

Alejandro Fernandez <ale@e-group.org> writes:
> and it says "stat failed on file '/home/ale/play/alertme.so': Permission denied"

> Permission? here are the file in question's permissions:
> -rwxrwxrwx

How about permissions on the directories in the path?

            regards, tom lane

Alejandro Fernandez <ale@e-group.org> writes:

> Hi,
>
> I have the short term problem of needing to know quite fast when
> someone updates or inserts a column in a table I have. It's something
> I hope to automate, but for now I've been looking around for a way to
> trigger a shell script to send me an email whenever this table is
> added to.
>
> I looked around on archives of over a year ago on this and other psql
> lists, and it all seemed to point to creating a function in C that is
> then added as a trigger to postgres. It seems strange to me that this
> little feature is not there, but I don't mind coding in C, so I
> thought I'd give it a go.
>
> I even found a great reference to this: Bruce Momjan's book,
> specifically, the little section on "extending postgresql in C".
>
> I followed this, created a little function that takes a char and
> returns the usual int, then I downloaded all of the newest version of
> postgresql, in response to
> http://www.ca.postgresql.org/docs/aw_pgsql_book/node170.html - and
> finally issued some dubious compilation commands to make the
> dynamically linked c file (is this too much work for a seemingly
> simple task like this??), based on the headers I'd just downloaded :
>
> gcc -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -fpic
> -I/opt/downloads/postgresql-7.2.1/src/interfaces/libpq -I
> /opt/downloads/postgresql-7.2.1/src/include/ -c -o alertme.o alertme.c
>
> and now I've tried the following command as user postgres, in psql:
>
> create function alertme(char) returns int as '/home/ale/c/alertme.so'
> language 'C';
>
> and it says "stat failed on file '/home/ale/play/alertme.so':
> Permission denied"
>
> Permission? here are the file in question's permissions: -rwxrwxrwx
>
> That's more than's even safe... What am I doing wrong??
>
> Thanks,
>
> Ale

Perhaps you should try using PostgreSQL's built-in features instead of
starting from scratch and building your own whatsit.  Depending on
what you need, there are two simple ways to solve your problem.

If you really need to know "right now" the best solution is to create
some type of long running program (a simple script should suffice)
that connects to PostgreSQL and listens for notifies.  You simply
create a trigger that fires a notify off on insert and your long
running script can then capture that notification and responds
accordingly.

If you can wait a bit you can go even more low tech.  Simply have cron
fire off a script every minute to check for new tuples.  New tuples
can either be marked with some sort of a boolean (requiring a
sequential scan) or you can store the primary keys of the new tuples
in a new_inserts table filled with a trigger.  Cron can pick up the
changes and react accordingly.

Hope this is helpful,

Jason

Thanks for the suggestions. I put it in /usr/lib/pgsql and now it's getting taken. I used the command

create function alertme(varchar(100)) returns int as '/usr/lib/pgsql/alertme.so' language 'C';

This seemed to work: it returned "CREATE"

Then commands like:
select alertme('This is not an exercise!');

would send me an email, although there seems to be a problem with the types, because it takes a char * as an argument,
butI couldn't find a list of types for postgresql functions, so the email it sends always contains one symbol (pi for
stringsand 3/4ths symbol for integers or chars), can't cut and paste it to show...). 

Anyway, last step was to create a trigger so that I could monitor changes to my table remotely:

create trigger trigger_alertme
before insert or update on mytable for each row execute procedure alertme('blablabla');

And this will not work: it returns:

ERROR:  CreateTrigger: function alertme() does not exist

Any idea where to go from here?

Thanks again! - I think this is quite a frequent wish by many people, as I've seen the question "how do I trigger an
outsidescript" crop up unanswered quite a few times in the archives. I did get the email about the sourceforge project
-thanks a lot! - but I'm so far into this, I'd like to get to the bottom of it first! (Yes, and meanwhile I'm using
cron- thanks!). 

Ale


On Thu, 02 May 2002 17:46:13 -0400
Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Alejandro Fernandez <ale@e-group.org> writes:
> > and it says "stat failed on file '/home/ale/play/alertme.so': Permission denied"
>
> > Permission? here are the file in question's permissions:
> > -rwxrwxrwx
>
> How about permissions on the directories in the path?
>
>             regards, tom lane


--
Alejandro Fernandez
Electronic Group Interactive
--+34-65-232-8086--

Le Jeudi 2 Mai 2002 17:30, Alejandro Fernandez a écrit :
> I have the short term problem of needing to know quite fast when someone
> updates or inserts a column in a table I have. It's something I hope to
> automate, but for now I've been looking around for a way to trigger a shell
> script to send me an email whenever this table is added to.

Why don't you use PLbash? There were lately discussions to include it in
PostgreSQL 7.3. Does anyone where to find PLbash source code and howto
compile it into PostgreSQL 7.2 ?

Cheers,
Jean-Michel

> Anyway, last step was to create a trigger so that I could monitor changes to my table remotely:
>
> create trigger trigger_alertme before insert or update on mytable for
> each row execute procedure alertme('blablabla');
>
> And this will not work: it returns:
>
> ERROR:  CreateTrigger: function alertme() does not exist

That's because trigger functions take no arguments and
return opaque.  Trigger arguments are passed specially
and not through normal arguments.  You need to write the
function to meet the trigger requirements (there's more information
in the documentation on trigger functions it looks like)