Re: best way to fetch next/prev record based on index - Mailing list pgsql-performance

From Merlin Moncure
Subject Re: best way to fetch next/prev record based on index
Date
Msg-id 6EE64EF3AB31D5448D0007DD34EEB34101AF05@Herge.rcsinc.local
Whole thread Raw
In response to best way to fetch next/prev record based on index  ("Merlin Moncure" <merlin.moncure@rcsonline.com>)
Responses Join performance
List pgsql-performance
> Greg Stark <gsstark@mit.edu> writes:
> > Removing <,<=,>,>= would be trivial.
>
> ... and not backwards-compatible.  If we did that then cases involving
> unlabeled row expressions would no longer work as they did in prior
> releases.  For example
>
>     select (1,2,3) < (4,5,6);
>
> is accepted by all releases back to 7.0, and probably much further
(but
> 7.0 is the oldest I have handy to test).  The only reason the code in
> parse_expr.c appears new is that the functionality used to be in
gram.y.
>
> I'd like to see this fixed to comply with the spec, but given the lack
> of complaints about the existing behavior over so many years, ripping
> it out meanwhile doesn't seem appropriate.

Just to clarify:
I think Greg is arguing to bring pg to SQL 92 spec and not remove
anything.  ISTM the SQL standard was designed with exactly my problem in
mind: how to get the next key in a table.

IMHO, relying
on select (1,2,3) < (4,5,6);
to give a result which is neither standard nor documented seems to be
bad style.  The current methodology could cause pg to give incorrect
results in TPC benchmarks...not good.  Also, it's trivial to rewrite
that comparison with the old behavior using 'and'.  OTOH, it is not
trivial to rewrite the comparison to do it the correct way...it's kind
of an SQL 'trick question'.  Most likely, a very small minority of pg
users are even away of the above syntax anyways.

To be fair, I'm a big fan of deprecating features for at least one
release for compatibility reasons.  It's no big deal to me, because I'm
already writing the queries out the long way anyways.  My interests are
in the optimizer.  If there is a way to enhance it so that it
multi-column comparisons in a logical way, that would be great.  Is this
theoretically possible (probable)?

Merlin


pgsql-performance by date:

Previous
From: Tom Lane
Date:
Subject: Re: best way to fetch next/prev record based on index
Next
From: Greg Stark
Date:
Subject: Re: best way to fetch next/prev record based on index