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