From: Eckhard Hoeffner <e-hoeffner@fifoost.org>
To: PHP PostgreSQL <pgsql-php@postgresql.org>
Subject: Re: using CURSOR with PHP
Date: Tue, 28 May 2002 18:16:03 +0200
Message-ID: <20020528161603.GA32455@fifoost.org> (raw)
In-Reply-To: <Pine.LNX.4.44.0205281714240.24472-100000@Megathlon.ESI>
References: <20020518110325.GA24074@fifoost.org>
<Pine.LNX.4.44.0205281714240.24472-100000@Megathlon.ESI>
* 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-php@postgresql.org
Cc: e-hoeffner@fifoost.org
Subject: Re: using CURSOR with PHP
In-Reply-To: <20020528161603.GA32455@fifoost.org>
References: <20020518110325.GA24074@fifoost.org> <Pine.LNX.4.44.0205281714240.24472-100000@Megathlon.ESI> <20020528161603.GA32455@fifoost.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox