Re: Before/After trigger sequencing questiont

Mike Nolan <nolan(at)gw(dot)tssi(dot)com> writes:
> 3. The 'after insert' trigger on the first table calls another procedure
> using plperlu which in turn executes an external PHP program that
> does a lookup on the 2nd table (using one of the updated values as
> a key) then sends some e-mail. It didn't find the record with the
> updated value.

> In thinking it through while typing typing this note, I think the problem
> is that the external PHP program doesn't see record with the updated value
> yet because the transaction hasn't been completed.

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.

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.

regards, tom lane