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

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 (view raw or flat)
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

pgsql-general by date

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

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