Re: Index with new opclass not used for sorting

On 21/06/2019 15:36, Tom Lane wrote:
> Ancoron Luciferis <ancoron(dot)luciferis(at)googlemail(dot)com> writes:
>> I am creating a new operator class for version 1 UUID's with an
>> extension and thought I was almost done by implementing everything
>> including SortSupport and creating a new opclass as follows:
>
>> CREATE OPERATOR CLASS uuid_timestamp_ops FOR TYPE uuid
>> USING btree AS
>> OPERATOR 1 <*,
>> ...
>
>> ...but when sorting on an (unique) index column, I still get a separate
>> sort, not using the index, e.g.:
>
> You did not show your test query, but I imagine it just asked for the
> type's ordinary sort order, which is not what this opclass is claiming
> to provide. To rely on the index's sort order you'd need something like
>
> select id from uuid_v1_ext
> where id <* '2b55fb04-33d8-11e9-9cfa-e03f494ffcf7'
> order by id using <* ;
>
> If you want this opclass to become the default sort order for uuid
> you'd have to remove the opcdefault marking from uuid_ops and attach
> it to this opclass instead. No, I'm not sure that that wouldn't have
> unpleasant side-effects.
>
> regards, tom lane
>

OK, I somehow feared I'd be getting such an answer.

But thanks a lot for the "using <*" trick, which I somehow didn't know
even exists. That solves the problem for me at least.

Because of this issue I was already thinking about creating a new data
type which is basically just a new name for the existing "uuid" but
would enforce version 1, for which I then could provide the opclass as
default, or?

Btw. is there some example how to create derived types for PG properly
without copy/paste of a lot of existing code?

Thanx a lot and cheers,

Ancoron