Re: browsing table with 2 million records

Christopher
> > - Present a nifty date selector to choose the records from any
day,
> > hour, minute, second
> > - show them, with "next day" and "previous day" buttons
> >
> > - It's more useful to the user (most likely he wants to know
what
> > happened on 01/05/2005 rather than view page 2857)
> > - It's faster (no more limit/offset ! just "date BETWEEN a AND
b",
> > indexed of course)
> > - no more new items pushing old ones to the next page while you
> browse
> > - you can pretend to your boss it's just like a paginated list
>
> All very well and good, but now do it generically...

I've done it...
First of all I totally agree with PFC's rant regarding absolute
positioning while browsing datasets. Among other things, it has serious
problems if you have multiple updating your table. Also it's kind of
silly to be doing this in a set based data paradigm.

The 'SQL' way to browse a dataset is by key. If your key has multiple
parts or you are trying to sort on two or more fields, you are supposed
to use the row constructor:

select * from t where (x, y) > (xc, yc) order by x,y;

Unfortunately, this gives the wrong answer in postgresql :(.

The alternative is to use boolean logic. Here is a log snippit from my
ISAM driver (in ISAM, you are *always* browsing datasets):

prepare system_read_next_menu_item_favorite_file_0 (character varying,
int4, int4, int4)
as select from system.menu_item_favorite_file
where mif_user_id >= $1 and
(mif_user_id > $1 or mif_menu_item_id >= $2) and
(mif_user_id > $1 or mif_menu_item_id > $2 or
mif_sequence_no > $3)
order by mif_user_id, mif_menu_item_id, mif_sequence_no
limit $4

This is a Boolean based 'get next record' in a 3 part key plus a
parameterized limit. You can do this without using prepared statements
of course but with the prepared version you can at least do

execute system_read_next_menu_item_favorite_file_0('abc', 1, 2, 1);

Merlin