Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1W16vh-0004zR-AT for pgsql-php@arkaria.postgresql.org; Thu, 09 Jan 2014 04:09:13 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1W16vg-0002A6-Lr for pgsql-php@arkaria.postgresql.org; Thu, 09 Jan 2014 04:09:12 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1W16vf-00029y-No for pgsql-php@postgresql.org; Thu, 09 Jan 2014 04:09:11 +0000 Received: from [2404:130:0:10::40:2] (helo=mail.catalyst.net.nz) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1W16vV-0007lE-N4 for pgsql-php@postgresql.org; Thu, 09 Jan 2014 04:09:10 +0000 Received: from localhost (localhost [127.0.0.1]) by mail.catalyst.net.nz (Postfix) with ESMTP id 7C44733260; Thu, 9 Jan 2014 17:08:55 +1300 (NZDT) X-Virus-Scanned: Debian amavisd-new at catalyst.net.nz Received: from mail.catalyst.net.nz ([127.0.0.1]) by localhost (bertrand.catalyst.net.nz [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id sfea1pGwNcZN; Thu, 9 Jan 2014 17:08:53 +1300 (NZDT) Received: from [IPv6:2404:130:0:1000:222:4dff:fe88:a586] (unknown [IPv6:2404:130:0:1000:222:4dff:fe88:a586]) (Authenticated sender: mark.kirkwood) by mail.catalyst.net.nz (Postfix) with ESMTPSA id A4C1B331B0; Thu, 9 Jan 2014 17:08:53 +1300 (NZDT) Message-ID: <52CE20D5.5050700@catalyst.net.nz> Date: Thu, 09 Jan 2014 17:08:53 +1300 From: Mark Kirkwood User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:24.0) Gecko/20100101 Thunderbird/24.2.0 MIME-Version: 1.0 To: Andrew McMillan , Eric Chamberlain CC: "pgsql-php@postgresql.org" Subject: Re: Unbuffered queries References: <1389227496.5900.128.camel@narbon.home.mcmillan.net.nz> In-Reply-To: <1389227496.5900.128.camel@narbon.home.mcmillan.net.nz> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit X-Host-Lookup-Failed: Reverse DNS lookup failed for 2404:130:0:10::40:2 (failed) X-Pg-Spam-Score: -1.1 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-php Precedence: bulk Sender: pgsql-php-owner@postgresql.org On 09/01/14 13:31, Andrew McMillan wrote: > On Wed, 2014-01-08 at 13:53 -0600, Eric Chamberlain wrote: >> When using php_query(), is this buffering all of the results from the >> query into memory? If so, is there a parameter I can send to make it >> not buffer the query? I've also seen comments suggesting that a cursor >> should be used. There doesn't seem to be any way to get access to the >> internal cursor used by the PHP pgsql libs... or are they referring to >> doing something like this: >> >> >> $result = pg_query($conn, "BEGIN; DECLARE s CURSOR FOR SELECT * FROM >> users; FETCH ALL IN s; END;"); >> >> >> And then after which I could do this: >> >> >> while ($row = pg_fetch_assoc($result)) { >> ... >> } >> >> >> I don't have a large enough result set in my development or QA >> environment to run this query within PHP to know if it works or not. >> Any suggestions would be helpful. Thank you! > You should *not* use the pg_* functions in PHP. Read up on PDO and use > that. > > http://php.net/pdo > > Using PDO you will get a 'PDOStatement' object to be the result of a > cusor-returning method (execute, query, prepare, ...), and then call > methods on that to 'fetch' or 'fetchObject' etc, etc. > > http://php.net/manual/en/class.pdostatement.php > > PDO is similar to Perl's DBI (and various other database independence > layers) and allows for statement construction with replaceable > parameters to avoid SQL insertion errors along with many, many more > features. > > pg_* should die in a fire. God has been killing a kitten every time is > has been used since 2003, which is unfortunately a lot of completely > avoidable kitten deaths :-( > I think a 2 step approach is needed - 1 statement to declare the cursor and execute it, another to explicitly call FETCH on it in a loop. I think attempting to do a FETCH ALL will just blow all your memory again. Here's a rough PDO example (I use FETCH 1 - FETCH n (n=100 say) is better, but for simplicity I'm doing just 1): $cursql = "DECLARE cur1 CURSOR FOR SELECT aid FROM pgbench_accounts WHERE bid = ?"; $sql = "FETCH 1 FROM cur1"; $dbh->beginTransaction(); $curstmt = $dbh->prepare($cursql); $curstmt->execute(array(rand(0, 100))); for ($i = 0; ; $i++) { $stmt = $dbh->prepare($sql); $stmt->execute(); $row = $stmt->fetch(PDO::FETCH_ASSOC); print "... " . $row['aid'] ."\n"; } -- Sent via pgsql-php mailing list (pgsql-php@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-php