X-Original-To: pgsql-php-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id DFF92D1B491 for ; Sat, 20 Dec 2003 02:31:35 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 10895-06 for ; Fri, 19 Dec 2003 22:30:49 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id AA916D1B47F for ; Fri, 19 Dec 2003 22:30:45 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.12.9/8.12.9) with ESMTP id hBK2UdU6097129 for ; Sat, 20 Dec 2003 02:30:39 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id hBK2OY2C087330 for pgsql-php@postgresql.org; Sat, 20 Dec 2003 02:24:34 GMT From: ljb X-Newsgroups: comp.databases.postgresql.interfaces.php Subject: How to cancel a query when user stops a page load Date: Sat, 20 Dec 2003 02:24:34 +0000 (UTC) Organization: Hub.Org Networking Services Lines: 53 Message-ID: X-Complaints-To: usenet@news.hub.org User-Agent: slrn/0.9.8.0 (Linux) To: pgsql-php@postgresql.org X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/53 X-Sequence-Number: 2499 This is in response to a message posted here asking how to have PHP cancel an in-progress PostgreSQL query when the user hits STOP on their web browser, or clicks to another page. Here is my solution. This is not a complete script, just fragments, but it should help. To the original poster: you can't just check pg_connection_busy() once, then call pg_get_result(), because pg_get_result will block waiting for the query to finish, and the user abort won't be seen. You must loop as shown below. [P.S. Do you really have a "select count(*) from test" which takes 2 minutes? Wow. I had trouble coming up with a test case taking 20 seconds on a PII-350MHz.] ......................................... # Make a global flag to indicate query in progress: $in_progress = 0; # Register a function to be called at shutdown: function halted() { global $db, $in_progress; if ($in_progress) { pg_cancel_query($db); } } register_shutdown_function('halted'); # Connect to the database and check for errors (OMITTED): $db = pg_connect(...); # Issue the query and set the flag telling the shutdown function to cancel it: if (!pg_send_query($db, $query)) { ... error handling omitted ... } $in_progress = 1; # Now we loop waiting for the query to complete or the user to cancel. # Display a message to the user telling how long it has been. flush(); $base = time(); while (pg_connection_busy($db)) { sleep(2); $delta = time() - $base; echo "
... $delta seconds\n"; flush(); } # All done, and it took $delta seconds. # Don't let the shutdown handler try to cancel the query: $in_progress = 0; # Fetch the query result: $r = pg_get_result($db); # Check $r for errors (omitted), display results (omitted). .........................................