Mailing-List: contact modperl-help@perl.apache.org; run by ezmlm
Precedence: bulk
Received-SPF: pass (asf.osuosl.org: local policy)
User-Agent: Microsoft-Entourage/11.2.3.060209
Date: Fri, 31 Mar 2006 07:21:59 -0500
Subject: Re: Database transaction across multiple web requests
From: Sean Davis <sdavis2@mail.nih.gov>
To: Tomas Zerolo <tomas@tuxteam.de>,
	"modperl@perl.apache.org" <modperl@perl.apache.org>
Message-ID: <C0528917.9376%sdavis2@mail.nih.gov>
Thread-Topic: Database transaction across multiple web requests
Thread-Index: AcZUvbUR8824zsCwEdqDXAANkzVl6A==
In-Reply-To: <20060331115956.GA23657@www.trapp.net>
Mime-version: 1.0
Content-type: text/plain;
	charset="US-ASCII"
Content-transfer-encoding: 7bit


On 3/31/06 6:59 AM, "Tomas Zerolo" <tomas@tuxteam.de> wrote:

> Howdy,
> 
> this is not strictly a modperl question, but I'm sure some of you are
> downright experts on this.
> 
> We have a modperl2 applicattion with a database backend (PostgreSQL,
> DBI). For session management we use Apache::Session.
> 
> Now the need has arisen to serve requests yielding many records as
> answers -- so the user wants to page through them, a bunch at a time,
> search-engine style.
> 
> As far as I see it, there are basically three options:
> 
> (a) Create a database cursor and page through it
> (b) Repeat the query (making sure there is a sort criterium) at each
>     page request, starting at a variable offset and limiting the
>     result set
> (c) Do the whole query at once, putting the results in some kind
>     of array.
> 
> Variant (a) seems most attractive -- the problem is PostgreSQL wants to
> keep a transaction open while the cursor is active (which is
> understandable, since the cursor is effectively a (partial) snapshot of
> the database). And the transaction is bound to the connection. So I'd
> have to make sure that subsequent web requests in one session "find" the
> same database connection. Has this been done? Or is this a "don't do
> that"?
> 
> There is not much to say on variants (b) and (c), I guess. The usual
> trade-offs, (b) generating more database activity and (c) being
> potentially a memory killer with the usual optimization tricks.
> 
> Thanks for any pointers, since I have been googling in vain for a while.

Tomas,

I can't see that a is viable in a web environment, but I'm no "expert".  For
starters, when does the transaction complete--are you going to trust users
to "log off" when they are done with a search?  It seems like if you are
dealing with enough traffic on your site to swamp a database backend,
keeping open that number of database connections open would be prohibitive.
Also, if you ever move from a one-server system to use a couple of machines,
anything that you can envision hacking together is then useless, as you
obviously can't have the same database handle open on multiple servers.

I think either B or C is probably going to be easiest (as you already know).
Have you benchmarked either b or c?

Also, you might have a look at this:

http://perlmonks.org/?node_id=492067

It probably falls into category b from above, but the AJAX component makes
this look like a pretty slick system.  I have never tried it, but I found it
intriguing.  

Sean