> Yeah, that would be my interpretation: the after trigger runs just
> before the transaction commits, and your external PHP program can't
> see the results since they haven't been committed yet. Your description
> makes it sound like the trigger invokes the PHP code synchronously,
> in which case it'd never work at all ... but if it's just asynchronously
> sending a message to make the PHP code run a bit later, then it would
> work almost all the time.
Actually, the perl program executes a batch file that has the PHP program
in it, so I can make it asynchronous by executing the PHP program as a
batch job (&) and then have a sleep(5) in it. Yeah, it's not very secure,
but since it executes as the postgres user anyone who can log in as
the root user or the postgres user could mess with it anyway.
> You might want to think about using LISTEN/NOTIFY somehow to trigger the
> PHP run. A listener is guaranteed not to get the notification until
> (and unless) the sending transaction commits.
I haven't tried figuring out LISTEN/NOTIFY yet.
I thought about using plperlu to generate the e-mail, but most of the
system is written in PHP. Also, In addition to sending the e-mail, it
uses curl to communicate with an external secure website, so it'd be a
lot of work to change it to perl, including escaping all the single
quotes so that it could be a PG function.
When I get this system finished (probably in October/November), I
really need to write it up for the website. IMHO it's a pretty
sophisticated example of what PG can do.
--
Mike Nolan