perl-modperl mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From to...@tuxteam.de (Tomas Zerolo)
Subject Database transaction across multiple web requests
Date Fri, 31 Mar 2006 11:59:56 GMT
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