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 F025BD1B46D for ; Mon, 22 Dec 2003 09:41:32 +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 87190-03 for ; Mon, 22 Dec 2003 05:40:45 -0400 (AST) Received: from ALPHA8.ITS.MONASH.EDU.AU (alpha8.its.monash.edu.au [130.194.1.8]) by svr1.postgresql.org (Postfix) with ESMTP id 76BDED1B48A for ; Mon, 22 Dec 2003 05:40:42 -0400 (AST) Received: from localhost ([130.194.13.85]) by vaxh.its.monash.edu.au (PMDF V5.2-31 #39306) with ESMTP id <01L4HRGN5S6O8X0TVE@vaxh.its.monash.edu.au> for pgsql-php@postgresql.org; Mon, 22 Dec 2003 10:22:13 +1100 Received: from broink.its.monash.edu.au (localhost.its.monash.edu.au [127.0.0.1]) by localhost (Postfix) with ESMTP id 2AA8515800B; Mon, 22 Dec 2003 10:22:13 +1100 (EST) Received: from med.monash.edu.au (cl-13B-103-akira-whisstock.med.monash.edu.au [130.194.193.147]) by broink.its.monash.edu.au (Postfix) with ESMTP id 0AC69120004; Mon, 22 Dec 2003 10:22:13 +1100 (EST) Date: Mon, 22 Dec 2003 10:24:33 +1100 From: Noel Subject: Re: How to cancel a query when user stops a page load In-reply-to: To: ljb Cc: pgsql-php@postgresql.org Message-id: <3FE62BB1.2050600@med.monash.edu.au> MIME-version: 1.0 Content-type: text/plain; format=flowed; charset=us-ascii Content-transfer-encoding: 7BIT User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.4) Gecko/20030624 Netscape/7.1 (ax) X-Accept-Language: en-us, en References: X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/61 X-Sequence-Number: 2507 It works great, Thanks heaps, so simple once you know how :). Cheers ljb wrote: >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.] > Counting through a table with >20mill rows, 20 times :), to show stats which are dynamic. > >......................................... > ># 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). > >......................................... > >---------------------------(end of broadcast)--------------------------- >TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > > Thanks once again :) -- Noel Faux Department of Biochemistry and Molecular Biology Monash University Clayton 3168 Victoria Australia