SEO

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

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

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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/

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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/i...e/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.

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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/i...e/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/i...l#XFUNC-C-PGXS

Thanks Michael. I am going to rewrite my Makefile using PGXS.

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster