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