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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-php
