perl-modperl mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Tom Schindl <tomAtLi...@gmx.at>
Subject Re: Database transaction across multiple web requests
Date Fri, 31 Mar 2006 12:17:57 GMT
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


Mime
View raw message