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)