Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1W18Fk-0007aZ-C0 for pgsql-php@arkaria.postgresql.org; Thu, 09 Jan 2014 05:34:00 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1W18Fj-0000KX-Go for pgsql-php@arkaria.postgresql.org; Thu, 09 Jan 2014 05:33:59 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1W18Fi-0000KQ-8I for pgsql-php@postgresql.org; Thu, 09 Jan 2014 05:33:58 +0000 Received: from blu0-omc1-s31.blu0.hotmail.com ([65.55.116.42]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1W18Fe-0002G7-6d for pgsql-php@postgresql.org; Thu, 09 Jan 2014 05:33:57 +0000 Received: from BLU168-W111 ([65.55.116.8]) by blu0-omc1-s31.blu0.hotmail.com with Microsoft SMTPSVC(6.0.3790.4675); Wed, 8 Jan 2014 21:33:53 -0800 X-TMN: [vK+mgAG1Omyvkxe0VHORrjdXP0iH4SO3] X-Originating-Email: [eric.chamberlain@hotmail.com] Message-ID: Content-Type: multipart/alternative; boundary="_cc39ce87-37db-4e26-81c2-592240d70633_" From: Eric Chamberlain To: Mark Kirkwood , Andrew McMillan CC: "pgsql-php@postgresql.org" Subject: Re: Unbuffered queries Date: Wed, 8 Jan 2014 23:33:53 -0600 Importance: Normal In-Reply-To: <52CE20D5.5050700@catalyst.net.nz> References: <1389227496.5900.128.camel@narbon.home.mcmillan.net.nz>, <52CE20D5.5050700@catalyst.net.nz> MIME-Version: 1.0 X-OriginalArrivalTime: 09 Jan 2014 05:33:53.0664 (UTC) FILETIME=[623F4800:01CF0CFC] X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-php Precedence: bulk Sender: pgsql-php-owner@postgresql.org --_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: mark.kirkwood@catalyst.net.nz > To: andrew@morphoss.com=3B eric.chamberlain@hotmail.com > CC: pgsql-php@postgresql.org > 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 (pgsql-php@postgresql.org) > 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
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.

Thank you for the response!

Eric Chamberlain

>=3B Date: Thu= =2C 9 Jan 2014 17:08:53 +1300
>=3B From: mark.kirkwood@catalyst.net.nz=
>=3B To: andrew@morphoss.com=3B eric.chamberlain@hotmail.com
>= =3B CC: pgsql-php@postgresql.org
>=3B Subject: Re: [PHP] Unbuffered qu= eries
>=3B
>=3B On 09/01/14 13:31=2C Andrew McMillan wrote:
&= gt=3B >=3B On Wed=2C 2014-01-08 at 13:53 -0600=2C Eric Chamberlain wrote:=
>=3B >=3B>=3B When using php_query()=2C is this buffering all of = the results from the
>=3B >=3B>=3B query into memory? If so=2C is = there a parameter I can send to make it
>=3B >=3B>=3B not buffer t= he query? I've also seen comments suggesting that a cursor
>=3B >=3B= >=3B should be used. There doesn't seem to be any way to get access to th= e
>=3B >=3B>=3B internal cursor used by the PHP pgsql libs... or a= re they referring to
>=3B >=3B>=3B doing something like this:
&= gt=3B >=3B>=3B
>=3B >=3B>=3B
>=3B >=3B>=3B $result = =3D pg_query($conn=2C "BEGIN=3B DECLARE s CURSOR FOR SELECT * FROM
>= =3B >=3B>=3B users=3B FETCH ALL IN s=3B END=3B")=3B
>=3B >=3B>= =3B
>=3B >=3B>=3B
>=3B >=3B>=3B And then after which I co= uld do this:
>=3B >=3B>=3B
>=3B >=3B>=3B
>=3B >=3B= >=3B while ($row =3D pg_fetch_assoc($result)) {
>=3B >=3B>=3B = ...
>=3B >=3B>=3B }
>=3B >=3B>=3B
>=3B >=3B>= =3B
>=3B >=3B>=3B I don't have a large enough result set in my dev= elopment or QA
>=3B >=3B>=3B environment to run this query within = PHP to know if it works or not.
>=3B >=3B>=3B Any suggestions woul= d be helpful. Thank you!
>=3B >=3B You should *not* use the pg_* fun= ctions in PHP. Read up on PDO and use
>=3B >=3B that.
>=3B >= =3B
>=3B >=3B http://php.net/pdo
>=3B >=3B
>=3B >=3B U= sing PDO you will get a 'PDOStatement' object to be the result of a
>= =3B >=3B cusor-returning method (execute=2C query=2C prepare=2C ...)=2C a= nd then call
>=3B >=3B methods on that to 'fetch' or 'fetchObject' e= tc=2C etc.
>=3B >=3B
>=3B >=3B http://php.net/manual/en/class= .pdostatement.php
>=3B >=3B
>=3B >=3B PDO is similar to Perl'= s DBI (and various other database independence
>=3B >=3B layers) and= allows for statement construction with replaceable
>=3B >=3B parame= ters to avoid SQL insertion errors along with many=2C many more
>=3B &= gt=3B features.
>=3B >=3B
>=3B >=3B pg_* should die in a fire= . God has been killing a kitten every time is
>=3B >=3B has been us= ed since 2003=2C which is unfortunately a lot of completely
>=3B >= =3B avoidable kitten deaths :-(
>=3B >=3B
>=3B
>=3B
&= gt=3B I think a 2 step approach is needed - 1 statement to declare the curs= or
>=3B and execute it=2C another to explicitly call FETCH on it in a= loop. I
>=3B think attempting to do a FETCH ALL will just blow all y= our memory again.
>=3B
>=3B Here's a rough PDO example (I use FE= TCH 1 - FETCH n (n=3D100 say) is
>=3B better=2C but for simplicity I'= m doing just 1):
>=3B
>=3B $cursql =3D "DECLARE cur1 CURSOR= FOR SELECT aid FROM pgbench_accounts
>=3B WHERE bid =3D ?"=3B
>= =3B $sql =3D "FETCH 1 FROM cur1"=3B
>=3B
>=3B $dbh-= >=3BbeginTransaction()=3B
>=3B $curstmt =3D $dbh->=3Bprepare(= $cursql)=3B
>=3B $curstmt->=3Bexecute(array(rand(0=2C 100)))=3B=
>=3B
>=3B for ($i =3D 0=3B =3B $i++) {
>=3B = $stmt =3D $dbh->=3Bprepare($sql)=3B
>=3B $stmt->=3Bexecut= e()=3B
>=3B $row =3D $stmt->=3Bfetch(PDO::FETCH_ASSOC)=3B>=3B print "... " . $row['aid'] ."\n"=3B
>=3B }
&= gt=3B
>=3B
>=3B
>=3B --
>=3B Sent via pgsql-php mai= ling list (pgsql-php@postgresql.org)
>=3B To make changes to your subs= cription:
>=3B http://www.postgresql.org/mailpref/pgsql-php
=
= --_cc39ce87-37db-4e26-81c2-592240d70633_--