Hi,
add a) You cann't do that because you are not able to keep connections
open between requests => find the appropriate connection
add b) That sounds the best option at least this is the one I chose the
last time you can do a LIMIT $start, $end (at least in MySQL). A
potential problem in this situation is that the datasource may
have been changed in the meantime ;-)
add c) I won't do that you'd need to push this array into a storage
system like BerkleyDB to share it between all you apache-children
Other ideas:
(d) When using Apache-2.2 you could implement Connection pooling
yourself (http://httpd.apache.org/docs/2.1/mod/mod_dbd.html). I've
once taken a look into it and found out that for DBD::MySQL you it
would be really simple to implement.
(e) Implement you're own backend-daemon e.g. using POE to do database
queries and hold the cursor
Tom
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.
>
> Regards
> -- tomas
signature.asc
Description: OpenPGP digital signature
