Re: Before/After trigger sequencing questiont

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Mike Nolan <nolan(at)gw(dot)tssi(dot)com>
Cc: pgsql-general(at)postgresql(dot)org (pgsql general list)
Subject: Re: Before/After trigger sequencing questiont
Date: 2004-07-28 14:14:04
Message-ID: 21495.1091024044@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Mike Nolan 2004-07-28 14:40:57 Re: Before/After trigger sequencing questiont
Previous Message Prabu Subroto 2004-07-28 14:13:50 Re: altering a starting value of "serial" macro