Re: using CURSOR with PHP

* Marco Colombo <marco@esi.it> [28 05 02 17:32]:

>On Sat, 18 May 2002, Eckhard Hoeffner wrote:
>
>> * Andrew McMillan <andrew@catalyst.net.nz> [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@ESI.it
>

--
--//--\\--
Eckhard Hoeffner
e-hoeffner@fifoost.org
Tal 44
D-80331 München