Re: Reverse collations (initially for making keyset pagination cover more cases)

Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk> writes:
> "Tom" == Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> Tom> Lastly, your proposed use-case has some attraction, but this
> Tom> proposal only supports it if the column you need to be differently
> Tom> sorted is textual. What if the sort columns are all numerics and
> Tom> timestamps?

> There are already trivial ways to reverse the orders of those, viz.
> (-number) and (-extract(epoch from timestampcol)). The lack of any
> equivalent method for text is what prompted this idea.

Those "trivial ways" have failure cases, eg with INT_MIN. I don't buy
that this argument justifies introducing a kluge into text comparison.

> Tom> Thinking about that, it seems like what we'd want is some sort of
> Tom> more-general notion of row comparison, to express "bounded below
> Tom> in an arbitrary ORDER BY ordering". Not quite sure what it ought
> Tom> to look like.

> Well, one obvious completely general method is to teach the planner
> (somehow) to spot conditions of the form
> (a > $1 OR (a = $1 AND b > $2) OR (a = $1 AND b = $2 AND c > $3) ...)
> etc. and make them indexable if the sense of the > or < operator at
> each step matched an ASC or DESC column in the index.

I think really the only attraction of that is that it could be argued
to be standard --- but I rather doubt that it's common for DBMSes to
recognize such things. It'd certainly be a royal pain in the rear
both to implement and to use, at least for more than about two sort
columns.

Back at
https://www.postgresql.org/message-id/10492.1531515255%40sss.pgh.pa.us
I proposed that we might consider allowing row comparisons to specify
an explicit list of operators:

>> One idea for resolving that is to extend the OPERATOR syntax to allow
>> multiple operator names for row comparisons, along the lines of
>> ROW(a,b) OPERATOR(pg_catalog.<, public.<) ROW(c,d)

I wonder whether it'd be feasible to solve this problem by doing that
and then allowing the operators to be of different comparison types,
that is "ROW(a,b) OPERATOR(<, >) ROW(c,d)". The semantics would be
that the first not-equal column pair determines the result according
to the relevant operator. But I'm not quite sure what to do if the
rows are in fact equal --- if some of the operators are like "<" and
some are like "<=", what should the result be? Maybe let the last
column's operator decide that?

regards, tom lane