[PHP] Unbuffered queries

[Eric Chamberlain]

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!

Eric Chamberlain

[Andrew McMillan]

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 :-(

Regards,
Andrew McMillan.

--
------------------------------------------------------------------------
andrew (AT) morphoss (DOT) com +64 (2) 7233 2426
Flexibility is overrated. Constraints are liberating.
------------------------------------------------------------------------




--
Sent via pgsql-php mailing list (pgsq...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-php

[Mark Kirkwood]

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";

[Eric Chamberlain]

Correct. I ended up limiting the number of records I query at a time. I'm not sure how much of the thread has been e-mailed to you but I have an example that shows the method in which I was able to query N number of records every iteration.

Thank you for the response!

Eric Chamberlain

> Date: Thu, 9 Jan 2014 17:08:53 +1300
> From: mark.k...@catalyst.net.nz
> To: and...@morphoss.com; eric.cha...@hotmail.com
> CC: pgsq...@postgresql.org
> Subject: Re: [PHP] Unbuffered queries