Thread: Unbuffered queries

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

Regards,
                    Andrew McMillan.

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

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

On 09/01/14 18:33, Eric Chamberlain wrote:
> 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.
>

Unfortunately I don't seem to have seen that message. However that
reminds me that for completeness I should really show an example
fetching >1 row at a time in PDO (pgbench schema again):

     $fetch_num = 100;
     $cursql = "DECLARE cur1 CURSOR FOR SELECT * FROM pgbench_accounts
WHERE bid = ?";
     $sql    = "FETCH $fetch_num FROM cur1";

     $dbh->beginTransaction();
     $curstmt = $dbh->prepare($cursql);
     $curstmt->execute(array(rand(0, 100)));

     for (;;) {
         $stmt = $dbh->prepare($sql);
         $stmt->execute();
         $rowarray = $stmt->fetchAll(PDO::FETCH_ASSOC);
         if ($rowarray) {
             foreach ($rowarray as $row) {
                 print "... " . $row['aid'] . " " . $row['bid'] . "\n";
             }
         } else {
             break;
         }
     }


Regards

Mark