|
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.kirkwood@xxxxxxxxxxxxxxx > To: andrew@xxxxxxxxxxxx; eric.chamberlain@xxxxxxxxxxx > CC: pgsql-php@xxxxxxxxxxxxxx > Subject: Re: Unbuffered queries > > 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 |
