Re: best way to retreive the next record in a multi column index

Bruno Wolff III wrote:
> How about something like the following:
> select * from t
> where a >= a1 and b >= b1
> order by a, b limit 1 offset 1;

Well, this may have recently changed, but the offset clause is not
suitable for arbitrary jumps over large tables. Essentially, pg does an
index lookup to the first element then sequential scans until the offset
criteria is met. Even if that was not the case there is another
problem: Suppose while you are iterating over your table another
backend deletes a row after your initial start position; this will cause
a record to get skipped! (unless inside a transaction, of course, but
that can't be assumed).

I also spent a lot of time thinking about use some type of concatenation
and functional indices to get around the multi column issue (then things
would be really simple!). This turned out to be a very complicated and
I ended up giving it up: I was stymied in the creation of a 'universal
concatenation' function, plus losing the elegant syntax to do partials
was a loss.

Merlin