* Marco Colombo <marco@xxxxxx> [28 05 02 17:32]: >On Sat, 18 May 2002, Eckhard Hoeffner wrote: > >> * Andrew McMillan <andrew@xxxxxxxxxxxxxxx> [18 05 02 12:36]: >> >> >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. >> >> >> > >> >I don't use cursors in web applications myself, since PostgreSQL >> >supports the excellent "SELECT ... LIMIT <n> OFFSET <m>" syntax in all >> >of the cases where I might otherwise have done so. >> >> That's what I wanted to avoid as this results in a little more >> work. >> >> >That's my experience to date, anyway - I'm prepared to find somewhere a >> >cursor might be a requirement - I know that if you wanted to write >> >cross-database portable code you would probably need to work with them. >> > >> >Regardless, unless you open your database connection as a persistent >> >connection, the database connection will be closed when the script >> >finishes, and that should clean up all that is necessary. >> >> I used persistent connections, however, I got lots of <defunct> >> processes. >> 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. > >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. > >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. >-- > ____/ ____/ / > / / / Marco Colombo > ___/ ___ / / Technical Manager > / / / ESI s.r.l. > _____/ _____/ _/ Colombo@xxxxxx > -- --//--\\-- Eckhard Hoeffner e-hoeffner@xxxxxxxxxxx Tal 44 D-80331 München