Thread: Triggers After INSERT
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...???? Stephane Tessier
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...???? 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: http://www.php.net/manual/en/function.pg-send-query.php Keep in mind however that you won't receive a success or failure when you do this. Greg
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@Yahoo.com #
Jan Wieck wrote: > Gregory Wood wrote: > > 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'm assuming (standard assumption disclaimers apply) that PHP will keep this connection open until the query returns, but to be honest, I just don't know. At the very least, the web developer is able to finish generating any necessary HTML to return to the browser. Although you might argue that without waiting for a result, what are they going to return... As for the web server terminating the process, I'm fairly certain that PHP does _not_ rollback a transaction when the connection is closed, at least when persistent sessions are used. I'd love for someone to tell me (factually) that it does in fact, but until someone can tell me otherwise, that is my understanding. > 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. I agree, but I'm also guessing that this user is just doing bulk imports that he wants to fire off from the browser and forget about. In which case it might be best to create a secondary, command line script that will perform the database work, and have the browser script kick off that process. It could even meta refresh itself to check for a status flag that tells it whether the process was successful or failed. Greg