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

On Fri, 2006-11-03 at 09:06 -0500, 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.
>

It's not that bad. You run the risk of getting inconsistent results if
the table is modified while the user is browsing between pages.

For instance, if someone views page one and gets records 0-9, and
someone else deletes the 3rd record, the 10th record will then move to
the first set of 10 results, and when the user clicks "next" he will get
11-20, and will never see 10. For some applications this matters, for
some it doesn't.

> 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

As long as you make sure not to leave an open transaction waiting for
user input. You can declare a cursor "WITH HOLD" which keeps it
available for that connection, but that doesn't seem like the best
answer in this case.

> option? Is this a better way to do this?

If the number of total results is somewhat small, run the query and
cache the primary keys in the session data. If a record gets deleted,
just show fewer results on that page, but keep any given result
consistently on the same page to prevent user confusion.

It really depends a lot on what you want to happen, and what is least
confusing to your users. Browsing a list of products for sale you would
want to be more accurate and up-to-date, so you want to ORDER BY a
timestamp or something, and then that way as the user browses any new
products are added to the end.

Regards,
Jeff Davis