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.
Regards
-- tomas
signature.asc
Description: Digital signature
