Re: Triggers After INSERT

Gregory Wood wrote:
> Stephane Tessier wrote:
>> Hi,
>>
>> I have a problem with triggers in postgresql 7.3.4 and PHP... I have a lot
>> of insert to do from a table x to table y ( average of 10000 rows each
>> time). I use a trigger AFTER INSERT but it seems that PHP wait for the
>> result of the trigger to ending the script... Is it possible to avoid
>> waiting for a return value and let the trigger do the job...????

An AFTER row trigger is not an asynchronous background job. The
execution of all statements in the trigger is still part of the
transaction. So your PHP script cannot expect any reply to this before
it either failed, or succeeded entirely (that's the A in ACID).

>
> It's not so much the trigger that's holding up your script, it's the
> fact that the query is executed synchronously. Although I've never done
> it before, you can send the query asynchronously using pg_send_query().
> I'm not sure that the script will complete while there are still queries
> pending (otherwise in the case of persistent connections, I can see this
> depleting the pool of connections), but it's worth a shot. For more
> information:

This won't work. What process will receive the final query result? And
what happens if the web server terminates the process before that? This
will lead to entirely rolled back transactions.

I think you want to do the entire aftermath of your PHP script in a
shutdown handler. That means, that the main script will start working on
the things up to the insert, but then registers a shutdown handler that
uses the db connection with the open transaction and finishes all the
work in the background.

Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #