Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id 7EFD7475F2F for ; Sat, 18 May 2002 15:39:38 -0400 (EDT) Received: from localhost (p50801895.dip.t-dialin.net [80.128.24.149]) by postgresql.org (Postfix) with ESMTP id AC240475AA8 for ; Sat, 18 May 2002 15:38:17 -0400 (EDT) Received: from eckhard by localhost with local (Exim 3.35 #1 (Debian)) id 179A1y-0008Tm-00 for ; Sat, 18 May 2002 21:38:18 +0200 Date: Sat, 18 May 2002 21:38:18 +0200 From: Eckhard Hoeffner To: Postgres-PHP Subject: Re: using CURSOR with PHP Message-ID: <20020518193818.GA32541@fifoost.org> Mail-Followup-To: Postgres-PHP References: <1021723791.30041.241.camel@kant.mcmillan.net.nz> 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/64 X-Sequence-Number: 1196 * Keary Suska [18 05 02 20:32]: >on 5/18/02 6:09 AM, andrew@catalyst.net.nz purportedly said: > >>> 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 >>=20 >> Right, so your problem is you're trying to use CURSOR, which you are >> leaving open on the off-chance your visitor will returns and wants to >> see the 'next record' or something. >>=20 >> I think that really you just should bite the bullet and use the better >> syntax on SELECT. You may have a little more programming work to do, >> but it has little impact on performance as far as I can see. >>=20 >> PostgreSQL will optimise queries differently when they have LIMIT >> clauses too. > >I'm afraid Andrew is correct here--with persistent connections, you have no >control, over when the connection is closed, and what connection you are >using at any one point. Thus you would still have to keep track of where t= he >cursor is, and furthermore you would have to check for the existence of the >cursor on every script invocation, which I don't believe there is a command >for, so you would have to try to use the cursor and then capture any error. >In the case of an error, you would need to re-define the cursor and move to >the current point according to the stored cursor location info. > >Depending on your program logic, this could be more difficult than using >LIMIT. Using LIMIT has the added benefit of avoiding the overhead of many >unused cursors, which can build up quickly as you also have little or no >control over the cursors since cursors are tied to the connection. > >The drawback to using LIMIT is that you are executing the query on every >call, and Postgres cannot optimize on a LIMIT as was mentioned, because the >entire query has to be collected before the LIMIT can be applied. However, >IIRC, Postgres does query result caching, so subsequent calls on the same >query will tend to be faster, providing there is enough memory allocated to >support it. > That's what I assumed, because the HTTP-protocol does not allow to establish persistent connections. The web server is sending the requested ducument and closes the connection. Each new request is a new connection as regards to the http protocol.=20 If you are using non persistent database connections, the CURSOR is closed when the script has finished.=20 If you are using persisten database connections you can establish an individual CURSOR for each user - using for example the user_id as name for the CURSOR (or a random number). Then, however, the user may shut down the browser and we still have the CURSOR.=20 Thanks for your help.=20 --=20 --//--\\-- Eckhard Hoeffner e-hoeffner@fifoost.org Tal 44=20 D-80331 M=FCnchen