Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id B8B17475964 for ; Tue, 28 May 2002 12:16:00 -0400 (EDT) Received: from localhost (p508008EC.dip.t-dialin.net [80.128.8.236]) by postgresql.org (Postfix) with ESMTP id 204A7475469 for ; Tue, 28 May 2002 12:15:59 -0400 (EDT) Received: from eckhard by localhost with local (Exim 3.35 #1 (Debian)) id 17Cjdj-0008SY-00 for ; Tue, 28 May 2002 18:16:03 +0200 Date: Tue, 28 May 2002 18:16:03 +0200 From: Eckhard Hoeffner To: PHP PostgreSQL Subject: Re: using CURSOR with PHP Message-ID: <20020528161603.GA32455@fifoost.org> Mail-Followup-To: PHP PostgreSQL References: <20020518110325.GA24074@fifoost.org> Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-15 Content-Disposition: inline Content-Transfer-Encoding: quoted-printable In-Reply-To: User-Agent: Mutt/1.3.28i X-Archive-Number: 200205/99 X-Sequence-Number: 1231 * Marco Colombo [28 05 02 17:32]: >On Sat, 18 May 2002, Eckhard Hoeffner wrote: > >> * Andrew McMillan [18 05 02 12:36]: >>=20 >> >Eckhard Hoeffner wrote: >> >>Does anyone has a solution to use the CURSOR function with PHP. The >> >>main problem - in my opinion - is, that the CURSOR must be closed >> >>and if the database is accessed from a web browser, this can not be >> >>ensured.=20 >> >> >> > >> >I don't use cursors in web applications myself, since PostgreSQL=20 >> >supports the excellent "SELECT ... LIMIT OFFSET " syntax in all= =20 >> >of the cases where I might otherwise have done so. >>=20 >> That's what I wanted to avoid as this results in a little more >> work.=20 >>=20 >> >That's my experience to date, anyway - I'm prepared to find somewhere a= =20 >> >cursor might be a requirement - I know that if you wanted to write=20 >> >cross-database portable code you would probably need to work with them. >> > >> >Regardless, unless you open your database connection as a persistent=20 >> >connection, the database connection will be closed when the script=20 >> >finishes, and that should clean up all that is necessary. >>=20 >> I used persistent connections, however, I got lots of >> processes.=20 >> If the CURSOR is closed when the script has finished (pg_connect), >> it makes no sense (for me) using it, because I can not move within >> the results.=20 > >AFAIK, a CURSOR may be used only inside a single transaction, not a >single session. So it can't be used to 'page' the output of a query, >for example. > >And, also AFAIK, even with pconnect, each connect creates a different >session. Only the (TCP) connection is re-used. I mean, nothing changes >from the PHP script standpoint when you switch from pg_connect() to >pg_pconnect(). I think, you are right. The connection browser<->web server is cut after the web server has send all TCP/IP-packages, no matter how long it takes till they are received on the client side (browser). If there ist a request for a new database connection, the web server does not know whether this is the former user or a new user. For this reason the result I wanted to acchive is not possible.=20 > >Quoting the PHP manual: > >" People who aren't thoroughly familiar with the way web servers > work and distribute the load may mistake persistent connects for what > they're not. In particular, they do not give you an ability to open > 'user sessions' on the same SQL link, they do not give you an ability > to build up a transaction efficently, and they don't do a whole lot > of other things. In fact, to be extremely clear about the subject, > persistent connections don't give you any functionality that wasn't > possible with their non-persistent brothers. " > >As I read it, the SQL session is reset anyway, no matter if you use >pg_connect() o pg_pconnect(). And any state saved on the DB backend >is lost, even if you're lucky enough so that your HTTP client connects >again to the same HTTP server process. This includes loosing any pending >transaction (I guess they are rollbacked) and so any cursor state. >I strongly suspect this includes any TEMP table as well (but I haven't >tried it). > >.TM. >--=20 > ____/ ____/ / > / / / Marco Colombo > ___/ ___ / / Technical Manager > / / / ESI s.r.l. > _____/ _____/ _/ Colombo@ESI.it > --=20 --//--\\-- Eckhard Hoeffner e-hoeffner@fifoost.org Tal 44=20 D-80331 M=FCnchen