Re: I know the bad way...what is the good way?

From: Richard Huxton <dev(at)archonet(dot)com>
To: Rick Schumeyer <rschumeyer(at)ieee(dot)org>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: I know the bad way...what is the good way?
Date: 2006-11-03 14:35:07
Message-ID: 454B539B.3060104@archonet.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Rick Schumeyer wrote:
> I confess to having once written code that prints something like
> "Items # 1 to 10 of 45"
> by using select count(*) from t where condition; and select * from t
> where condition limit 10 offset x;
>
> I now know this is "bad", I guess because of the count() and the offset.

Well, it's simple to understand, so it's good in that respect. Of
course, if someone inserts a row that matches your WHERE clause then
that can upset your counting.

> So what is the preferred way? If it matters, my new application is
> servlet based, so I believe (but not positive) this means cursors are an
> option? Is this a better way to do this?

You can have a scrollable cursor and page back and fore. This is only
do-able for the length of one connection of course.

You can have a table holding matches and page through that. The table
can hold all columns of the results (if they need to stay the same
regardless of changes in the database) or just the foreign-keys to reach
the relevant tables. The data in the table can sit there as long as you
like of course, and be shared amongst connections.

Finally, you can cache the data outside the database. Perfectly viable
for many situations, but not good if you routinely get a million
matches. Excellent for lists like "today's stories" that many clients
want and are reasonably sized.

Or, if you don't have that much activity, your "bad" approach is
perfectly fine of course.
--
Richard Huxton
Archonet Ltd

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Luca Ferrari 2006-11-03 14:36:04 problem accessing a database
Previous Message Magnus Hagander 2006-11-03 14:33:27 Re: Counting records in a PL/pgsql cursor