Skip site navigation (1) Skip section navigation (2)

Re: Before/After trigger sequencing questiont

From: Mike Nolan <nolan(at)gw(dot)tssi(dot)com>
To: tgl(at)sss(dot)pgh(dot)pa(dot)us (Tom Lane)
Cc: pgsql-general(at)postgresql(dot)org (pgsql general list)
Subject: Re: Before/After trigger sequencing questiont
Date: 2004-07-28 14:40:57
Message-ID: 200407281440.i6SEevCm009193@gw.tssi.com (view raw or flat)
Thread:
Lists: pgsql-general
> 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

In response to

pgsql-general by date

Next:From: Daniel VeriteDate: 2004-07-28 14:45:10
Subject: Re: Loading/Retrieving bytea fields?
Previous:From: Tom LaneDate: 2004-07-28 14:14:04
Subject: Re: Before/After trigger sequencing questiont

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group