Re: best way to fetch next/prev record based on index

"Merlin Moncure" <merlin(dot)moncure(at)rcsonline(dot)com> writes:

> > do it for multi-column keys. It seems it would be nice if some syntax
> > similar to (a,b,c) > (a1,b1,c1) worked for this.
>
> 'nice' would be an understatement...
>
> if the above syntax is not defined in the standard, I would humbly suggest,
> well, beg for it to work as you thought it did. That would be GREAT! ISMT it
> may be that that is in fact standard...(I don't have it, so I don't know).

Hum. It would seem my intuition matches the SQL92 spec and Postgres gets this
wrong.

From page 208 (Section 8.2.7) of
http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

7) Let Rx and Ry be the two <row value constructor>s of the <com-
parison predicate> and let RXi and RYi be the i-th <row value
constructor element>s of Rx and Ry, respectively. "Rx <comp op>
Ry" is true, false, or unknown as follows:

a) "x = Ry" is true if and only if RXi = RYi for all i.

b) "x <> Ry" is true if and only if RXi <> RYi for some i.

c) "x < Ry" is true if and only if RXi = RYi for all i < n and
RXn < RYn for some n.

d) "x > Ry" is true if and only if RXi = RYi for all i < n and
RXn > RYn for some n.

...

(This is A July 10, 1992 Proposed revision, I don't know how far it differs
from the final. I imagine they mean "Rx" in all the places they use "x" alone)

That fairly clearly specifies (a,b,c) < (a1,b1,c1) to work the way you want it
to. Less-than-or-equal is then defined based on the above definition.

Even if Postgres did this right I'm not sure that would solve your index woes.
I imagine the first thing Postgres would do is rewrite it into regular scalar
expressions. Ideally the optimizer should be capable of then deducing from the
scalar expressions that an index scan would be useful.

--
greg