perl-modperl mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Sean Davis <>
Subject Re: Database transaction across multiple web requests
Date Fri, 31 Mar 2006 12:21:59 GMT

On 3/31/06 6:59 AM, "Tomas Zerolo" <> 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.


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:

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


View raw message