pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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



view thread (23+ messages)  latest in thread

Message-ID: <20020528161603.GA32455@fifoost.org>
Permalink:  ../20020528161603.GA32455@fifoost.org/
Also on:    postgresql.org/message-id/20020528161603.GA32455@fifoost.org

 · 

reply

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