Before/After trigger sequencing question

Mike Nolan <nolan@gw.tssi.com> writes:

I have a before insert trigger that updates a value in another table.

It appears that I cannot depend upon that update having taken place
in an after insert trigger on the first table. (The one with the insert.)

This seems a tad improbable, not to say impossible. Concrete example,
please?

regards, tom lane

Mike Nolan <nolan@gw.tssi.com> writes:

I have a before insert trigger that updates a value in another table.

It appears that I cannot depend upon that update having taken place
in an after insert trigger on the first table. (The one with the insert.)

This seems a tad improbable, not to say impossible. Concrete example,
please?

I can't reproduce it using a simple example, but here's the sequence
of events that happened this morning (on 7.4.1):

1. A record was inserted into a table with about a million rows in it.
2. This insert triggered a before insert procedure that updated several
values in a second table, one with about 580,000 rows in it.
(This was via several different update statements in the trigger
function.)
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.
--
Mike Nolan

I think you're right : the transaction which updated the rows is not
commited yet when you call your external php procedure, and thus it does
not see the updated rows.

This is tricky because you can't commit in a plsql function.

You could add the emails to be sent to a table, which would be looked up
by a cron task sending emails and deleting the records afterwards.

You could have your perl function (which runs inside your transaction)
pass the data tot the PHP script.

You could also send your email from Perl.

Mike Nolan <nolan@gw.tssi.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

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