pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Eric Chamberlain <eric.chamberlain@hotmail.com>
To: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
To: Andrew McMillan <andrew@morphoss.com>
Cc: pgsql-php@postgresql.org <pgsql-php@postgresql.org>
Subject: Re: Unbuffered queries
Date: Wed, 8 Jan 2014 23:33:53 -0600
Message-ID: <BLU168-W111F40FD879BDC092DB4AC684B00@phx.gbl> (raw)
In-Reply-To: <52CE20D5.5050700@catalyst.net.nz>
References: <BLU168-W6E6F9F54342A13F29A86484B10@phx.gbl>
	<1389227496.5900.128.camel@narbon.home.mcmillan.net.nz>
	<52CE20D5.5050700@catalyst.net.nz>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-php>

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@catalyst.net.nz
> To: andrew@morphoss.com; eric.chamberlain@hotmail.com
> CC: pgsql-php@postgresql.org
> Subject: Re: [PHP] 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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-php
 		 	   		  =

view thread (5+ messages)  latest in thread

Message-ID: <BLU168-W111F40FD879BDC092DB4AC684B00@phx.gbl>
Permalink:  ../BLU168-W111F40FD879BDC092DB4AC684B00@phx.gbl/
Also on:    postgresql.org/message-id/BLU168-W111F40FD879BDC092DB4AC684B00@phx.gbl

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-php@postgresql.org
  Cc: eric.chamberlain@hotmail.com, mark.kirkwood@catalyst.net.nz, andrew@morphoss.com
  Subject: Re: Unbuffered queries
  In-Reply-To: <BLU168-W111F40FD879BDC092DB4AC684B00@phx.gbl>
  References: <BLU168-W6E6F9F54342A13F29A86484B10@phx.gbl> <1389227496.5900.128.camel@narbon.home.mcmillan.net.nz> <52CE20D5.5050700@catalyst.net.nz> <BLU168-W111F40FD879BDC092DB4AC684B00@phx.gbl>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox