Обсуждение: C trigger using system(3) to execute a php script
Hi there, I have written a trigger in C for a postgresql engine (8.0.1) running on a FreeBSD 5.4-RELEASE i386 machine. I mainly used the example code from the documentation. The trigger is working perfectly and it is triggered when I want to. Then, at some point, I wanted to call an external php script from the trigger to perform some sql statements against the database. Before trying that I started writing a very simple php script which basically created a file in /tmp. It worked. Then, I wanted to run the full php script from the trigger. I tried the script manually, form the shell, and it worked perfectly. Notice that the script doesn't generate any stdin/stdout output. So I changed the system(3) parameter to call my php script instead of the test one. I tried it and it didn't work. The php script doesn't perform the modifications it suppose to in the database. The php script is about 150 lines long and performs like 5 or 6 sql queries/inserts against the database. Can anyone advice me with these? Thanks in advance, David
David Rio Deiros <driodeiros@gmail.com> writes: > Then, I wanted to run the full php script from the trigger. I tried > the script manually, form the shell, and it worked perfectly. Notice > that the script doesn't generate any stdin/stdout output. So I changed > the system(3) parameter to call my php script instead of the test one. > I tried it and it didn't work. The php script doesn't perform the > modifications it suppose to in the database. > > The php script is about 150 lines long and performs like 5 or 6 sql > queries/inserts against the database. Why would you do it that way? The queries/inserts won't be rolled back if the transaction containing the trigger aborts (because they'd be done in a separate session). Why not just do your queries from C using the SPI interface? -Doug
On Thu, Feb 16, 2006 at 02:24:06PM -0500, Douglas McNaught wrote: > David Rio Deiros <driodeiros@gmail.com> writes: > > > Then, I wanted to run the full php script from the trigger. I tried > > the script manually, form the shell, and it worked perfectly. Notice > > that the script doesn't generate any stdin/stdout output. So I changed > > the system(3) parameter to call my php script instead of the test one. > > I tried it and it didn't work. The php script doesn't perform the > > modifications it suppose to in the database. > > > > The php script is about 150 lines long and performs like 5 or 6 sql > > queries/inserts against the database. > > Why would you do it that way? The queries/inserts won't be rolled > back if the transaction containing the trigger aborts (because they'd > be done in a separate session). Why not just do your queries from C > using the SPI interface? The code was already in php... plus I am including some other php files so I would have to parse that files from C if I write all in C. I basically didn't want to do extra work. Thanks for the answer, David
David Rio Deiros <driodeiros@gmail.com> writes: > The code was already in php... plus I am including some other php > files so I would have to parse that files from C if I write all in C. I > basically didn't want to do extra work. There's a PL/PHP out there, but I don't know if it's sufficiently advanced to be able to write triggers in it... -Doug
Douglas McNaught wrote: > David Rio Deiros <driodeiros@gmail.com> writes: > >> The code was already in php... plus I am including some other php >> files so I would have to parse that files from C if I write all in C. I >> basically didn't want to do extra work. > > There's a PL/PHP out there, but I don't know if it's sufficiently > advanced to be able to write triggers in it... Yes quite... you can write triggers, srf functions, polymorphic types, all kinds of stuff. Review: http://projects.commandprompt.com/projects/public/plphp Sincerely, Joshua D. Drake > > -Doug > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster -- The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/
Hi there, I just wanted to let you know that I finally made the trigger work using an external php script. Actually it always worked, the problem is that I was trying the trigger in one machine and testing the results in other. I know, very lame. Some people suggested plphp, I tried it but I had issues trying to compile it. But the project looks promising anyway. Also, I took me a while to figure out how to compile my trigger on FreeBSD and MacOSX/Darwin. Finally I found this in the documentation: http://www.postgresql.org/docs/8.0/interactive/xfunc-c.html which perfectly explains what flags to pass to gcc in order to compile your code on those and other platforms. I didn't find any link or reference to that document in the trigger section. Do you think it would be a good idea to do that? Thanks, David > David Rio Deiros <driodeiros@gmail.com> writes: > > > Then, I wanted to run the full php script from the trigger. I tried > > the script manually, form the shell, and it worked perfectly. Notice > > that the script doesn't generate any stdin/stdout output. So I changed > > the system(3) parameter to call my php script instead of the test one. > > I tried it and it didn't work. The php script doesn't perform the > > modifications it suppose to in the database. > > > > The php script is about 150 lines long and performs like 5 or 6 sql > > queries/inserts against the database.
On Sat, Feb 18, 2006 at 10:59:16AM -0800, David Rio Deiros wrote: > Also, I took me a while to figure out how to compile my trigger on > FreeBSD and MacOSX/Darwin. Finally I found this in the documentation: > > http://www.postgresql.org/docs/8.0/interactive/xfunc-c.html > > which perfectly explains what flags to pass to gcc in order to > compile your code on those and other platforms. Take a look at "Extension Building Infrastructure" on that page and also at the contrib modules' Makefiles. In most cases you can let PostgreSQL figure out how to build your code. http://www.postgresql.org/docs/8.0/interactive/xfunc-c.html#XFUNC-C-PGXS -- Michael Fuhr
On Sat, Feb 18, 2006 at 12:34:23PM -0700, Michael Fuhr wrote: > On Sat, Feb 18, 2006 at 10:59:16AM -0800, David Rio Deiros wrote: > > Also, I took me a while to figure out how to compile my trigger on > > FreeBSD and MacOSX/Darwin. Finally I found this in the documentation: > > > > http://www.postgresql.org/docs/8.0/interactive/xfunc-c.html > > > > which perfectly explains what flags to pass to gcc in order to > > compile your code on those and other platforms. > > Take a look at "Extension Building Infrastructure" on that page and > also at the contrib modules' Makefiles. In most cases you can let > PostgreSQL figure out how to build your code. > > http://www.postgresql.org/docs/8.0/interactive/xfunc-c.html#XFUNC-C-PGXS Thanks Michael. I am going to rewrite my Makefile using PGXS.