Re: using CURSOR with PHP

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().

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