Re: Unbuffered queries
Eric Chamberlain <[email protected]> Wed, 8 Jan 2014 23:33:53 -0600
| Newsgroups | gmane.comp.db.postgresql.php |
|---|---|
| Message-ID | <[email protected]> |
--_cc39ce87-37db-4e26-81c2-592240d70633_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Correct. I ended up limiting the number of records I query at a time. I'm n= ot sure how much of the thread has been e-mailed to you but I have an examp= le that shows the method in which I was able to query N number of records e= very iteration. Thank you for the response! Eric Chamberlain > Date: Thu=2C 9 Jan 2014 17:08:53 +1300 > From: [email protected] > To: [email protected]=3B [email protected] > CC: [email protected] > Subject: Re: [PHP] Unbuffered queries >=20 > On 09/01/14 13:31=2C Andrew McMillan wrote: > > On Wed=2C 2014-01-08 at 13:53 -0600=2C Eric Chamberlain wrote: > >> When using php_query()=2C is this buffering all of the results from th= e > >> query into memory? If so=2C 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 =3D pg_query($conn=2C "BEGIN=3B DECLARE s CURSOR FOR SELECT * = FROM > >> users=3B FETCH ALL IN s=3B END=3B")=3B > >> > >> > >> And then after which I could do this: > >> > >> > >> while ($row =3D 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=2C query=2C prepare=2C ...)=2C and then= call > > methods on that to 'fetch' or 'fetchObject' etc=2C 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=2C many more > > features. > > > > pg_* should die in a fire. God has been killing a kitten every time is > > has been used since 2003=2C which is unfortunately a lot of completely > > avoidable kitten deaths :-( > > >=20 >=20 > I think a 2 step approach is needed - 1 statement to declare the cursor=20 > and execute it=2C another to explicitly call FETCH on it in a loop. I=20 > think attempting to do a FETCH ALL will just blow all your memory again. >=20 > Here's a rough PDO example (I use FETCH 1 - FETCH n (n=3D100 say) is=20 > better=2C but for simplicity I'm doing just 1): >=20 > $cursql =3D "DECLARE cur1 CURSOR FOR SELECT aid FROM pgbench_account= s=20 > WHERE bid =3D ?"=3B > $sql =3D "FETCH 1 FROM cur1"=3B >=20 > $dbh->beginTransaction()=3B > $curstmt =3D $dbh->prepare($cursql)=3B > $curstmt->execute(array(rand(0=2C 100)))=3B >=20 > for ($i =3D 0=3B =3B $i++) { > $stmt =3D $dbh->prepare($sql)=3B > $stmt->execute()=3B > $row =3D $stmt->fetch(PDO::FETCH_ASSOC)=3B > print "... " . $row['aid'] ."\n"=3B > } >=20 >=20 >=20 > --=20 > Sent via pgsql-php mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-php = --_cc39ce87-37db-4e26-81c2-592240d70633_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <html> <head> <style><!-- .hmmessage P { margin:0px=3B padding:0px } body.hmmessage { font-size: 12pt=3B font-family:Calibri } --></style></head> <body class=3D'hmmessage'><div dir=3D'ltr'><span style=3D"color: rgb(34=2C = 34=2C 34)=3B font-family: arial=3B font-size: 10pt=3B">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 me= thod in which I was able to query N number of records every iteration.</spa= n><div style=3D"color: rgb(34=2C 34=2C 34)=3B font-family: arial=3B font-si= ze: small=3B"><br></div><div style=3D"color: rgb(34=2C 34=2C 34)=3B font-fa= mily: arial=3B font-size: small=3B">Thank you for the response!</div><div s= tyle=3D"color: rgb(34=2C 34=2C 34)=3B font-family: arial=3B font-size: smal= l=3B"><br></div><div style=3D"color: rgb(34=2C 34=2C 34)=3B font-family: ar= ial=3B font-size: small=3B">Eric Chamberlain</div><br><div>>=3B Date: Thu= =2C 9 Jan 2014 17:08:53 +1300<br>>=3B From: [email protected]= <br>>=3B To: [email protected]=3B [email protected]<br>>= =3B CC: [email protected]<br>>=3B Subject: Re: [PHP] Unbuffered qu= eries<br>>=3B <br>>=3B On 09/01/14 13:31=2C Andrew McMillan wrote:<br>&= gt=3B >=3B On Wed=2C 2014-01-08 at 13:53 -0600=2C Eric Chamberlain wrote:= <br>>=3B >=3B>=3B When using php_query()=2C is this buffering all of = the results from the<br>>=3B >=3B>=3B query into memory? If so=2C is = there a parameter I can send to make it<br>>=3B >=3B>=3B not buffer t= he query? I've also seen comments suggesting that a cursor<br>>=3B >=3B= >=3B should be used. There doesn't seem to be any way to get access to th= e<br>>=3B >=3B>=3B internal cursor used by the PHP pgsql libs... or a= re they referring to<br>>=3B >=3B>=3B doing something like this:<br>&= gt=3B >=3B>=3B<br>>=3B >=3B>=3B<br>>=3B >=3B>=3B $result = =3D pg_query($conn=2C "BEGIN=3B DECLARE s CURSOR FOR SELECT * FROM<br>>= =3B >=3B>=3B users=3B FETCH ALL IN s=3B END=3B")=3B<br>>=3B >=3B>= =3B<br>>=3B >=3B>=3B<br>>=3B >=3B>=3B And then after which I co= uld do this:<br>>=3B >=3B>=3B<br>>=3B >=3B>=3B<br>>=3B >=3B= >=3B while ($row =3D pg_fetch_assoc($result)) {<br>>=3B >=3B>=3B = ...<br>>=3B >=3B>=3B }<br>>=3B >=3B>=3B<br>>=3B >=3B>= =3B<br>>=3B >=3B>=3B I don't have a large enough result set in my dev= elopment or QA<br>>=3B >=3B>=3B environment to run this query within = PHP to know if it works or not.<br>>=3B >=3B>=3B Any suggestions woul= d be helpful. Thank you!<br>>=3B >=3B You should *not* use the pg_* fun= ctions in PHP. Read up on PDO and use<br>>=3B >=3B that.<br>>=3B >= =3B<br>>=3B >=3B http://php.net/pdo<br>>=3B >=3B<br>>=3B >=3B U= sing PDO you will get a 'PDOStatement' object to be the result of a<br>>= =3B >=3B cusor-returning method (execute=2C query=2C prepare=2C ...)=2C a= nd then call<br>>=3B >=3B methods on that to 'fetch' or 'fetchObject' e= tc=2C etc.<br>>=3B >=3B<br>>=3B >=3B http://php.net/manual/en/class= .pdostatement.php<br>>=3B >=3B<br>>=3B >=3B PDO is similar to Perl'= s DBI (and various other database independence<br>>=3B >=3B layers) and= allows for statement construction with replaceable<br>>=3B >=3B parame= ters to avoid SQL insertion errors along with many=2C many more<br>>=3B &= gt=3B features.<br>>=3B >=3B<br>>=3B >=3B pg_* should die in a fire= . God has been killing a kitten every time is<br>>=3B >=3B has been us= ed since 2003=2C which is unfortunately a lot of completely<br>>=3B >= =3B avoidable kitten deaths :-(<br>>=3B >=3B<br>>=3B <br>>=3B <br>&= gt=3B I think a 2 step approach is needed - 1 statement to declare the curs= or <br>>=3B and execute it=2C another to explicitly call FETCH on it in a= loop. I <br>>=3B think attempting to do a FETCH ALL will just blow all y= our memory again.<br>>=3B <br>>=3B Here's a rough PDO example (I use FE= TCH 1 - FETCH n (n=3D100 say) is <br>>=3B better=2C but for simplicity I'= m doing just 1):<br>>=3B <br>>=3B $cursql =3D "DECLARE cur1 CURSOR= FOR SELECT aid FROM pgbench_accounts <br>>=3B WHERE bid =3D ?"=3B<br>>= =3B $sql =3D "FETCH 1 FROM cur1"=3B<br>>=3B <br>>=3B $dbh-= >=3BbeginTransaction()=3B<br>>=3B $curstmt =3D $dbh->=3Bprepare(= $cursql)=3B<br>>=3B $curstmt->=3Bexecute(array(rand(0=2C 100)))=3B= <br>>=3B <br>>=3B for ($i =3D 0=3B =3B $i++) {<br>>=3B = $stmt =3D $dbh->=3Bprepare($sql)=3B<br>>=3B $stmt->=3Bexecut= e()=3B<br>>=3B $row =3D $stmt->=3Bfetch(PDO::FETCH_ASSOC)=3B<b= r>>=3B print "... " . $row['aid'] ."\n"=3B<br>>=3B }<br>&= gt=3B <br>>=3B <br>>=3B <br>>=3B -- <br>>=3B Sent via pgsql-php mai= ling list ([email protected])<br>>=3B To make changes to your subs= cription:<br>>=3B http://www.postgresql.org/mailpref/pgsql-php<br></div> = </div></body> </html>= --_cc39ce87-37db-4e26-81c2-592240d70633_--