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

David Fetter <david(at)fetter(dot)org> writes:
> Please find attached a patch for $Subject.

I think there's a reason why this hasn't been proposed before.

Back before we had full support of ASC/DESC index sort order, there was
interest in having reverse-sort operator classes, and there are bits and
pieces still in the code that tried to cater to that. But we never got
it to the point where such things would really be pleasant to use.
Now that we have ASC/DESC indexes, there's no value in a reverse-sort
operator class, so the idea's pretty much been consigned to the dustbin.

This looks to me like it's trying to go down that same path at the
collation level, and it seems like just as bad of an idea here.

The fundamental problem with what you propose is that it'd require
a bunch of infrastructure (which you haven't even attempted) to teach
the planner about the relationships between forward- and reverse-sort
collation pairs, so that it could figure out that scanning some index
backwards would satisfy a request for the reverse-sort collation,
or vice versa. Without such infrastructure, the feature is really
just a gotcha, because queries won't get optimized the way users
would expect them to.

And no, I don't think we should accept the feature and then go write
that infrastructure. If we couldn't make it work well at the opclass
level, I don't think things will go better at the collation level.

Lastly, your proposed use-case has some attraction, but this proposal
only supports it if the column you need to be differently sorted is
textual. What if the sort columns are all numerics and timestamps?
Thinking about that, it seems like what we'd want is some sort of
more-general notion of row comparison, to express "bounded below in
an arbitrary ORDER BY ordering". Not quite sure what it ought to
look like.

regards, tom lane