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