Re: Removing SORTFUNC_LT/REVLT

On Fri, Dec 30, 2005 at 10:18:48AM -0500, Tom Lane wrote:
> I really need to study your mail from the other day, but unfortunately
> other pressures will probably keep me from getting to it today :-(.
> One comment though --- it's not really sane to include ASC/DESC in there
> is it?  I thought the spec wanted ORDER BY foo COLLATE bar [ASC/DESC]
> ... or if not, users certainly will.  If every single collation has to
> be created in a matched ASC/DESC pair, you've done it wrong.

Well, that's an interesting question. From a user point of view,
forward and backwards isn't a real big deal. Internally it is though.
Two places really care:

- Pathkeys
- Index keys

For pathkeys in particular, I really don't like the propect of going
through all the planning code changing a single pathkey (which is
currently just the oid of the sorting operator) to a
collation plus direction pair.

Index keys are similar, though the code to change it not as much. We'd
still need to invent another column to store the direction.

For users ofcourse, we want them to look similar. Currently forward and
backward collations have different names and different OIDs, though
it's quite reasonable to give them the same name and require the user
to say DESC to access the reverse collation (still with different
OIDs).

However, the real reason I think they should both be declared is that
as actual objects, they don't resemble eachother very much. For
example, an int4 forward collation can be merge joined with an int2
forward collation but not with an int2 backward collation. When you
start considering relationships between collations, forwards and
backwards behave very differently.

Final food for thought: does the "xid" type have a collation. There's
no b-tree operator class but it does have the concept of equality. It
has a valid hashing function. Is this a collation? If so it has no
order (so ASC/DESC don't apply). Yet hashing is also a property of the
collation, not the type. The same string in different locales would
hash differently.

Where this heads is that a user creates a type, then a collation by
specifying an order and a hash function. The system then creates the
operator classes in the background. This is a radical departure from
where we are now and not something I'm seriously proposing.

Don't worry about how long it takes to think about it. 'tis the season
to be jolly, no? I myself will be offline for most of next week anyway
and the next few days are to be enjoyed.

Seasons greetings,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.