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

Alejandro Fernandez <ale(at)e-group(dot)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