Re: order by, for custom types

Hi folks

I have all operators required for b-tree, gist up here, and gist
index defined. But still "order by custom_type" won't work.
I have kind of wild card masks in my type definition, so b-tree won't
work. But still, do I need to define b-tree index as such for "order
by" to work ?
Perhaps gist should be expanded so it would take care of "order by"
and "distinct" ?

Thanks.

--
GJ

"If we knew what we were doing, it wouldn't be called Research, would
it?" - AE

On Fri, 18 Nov 2005, Grzegorz Jaskiewicz wrote:

> Hi folks
>
> I have all operators required for b-tree, gist up here, and gist index
> defined. But still "order by custom_type" won't work.
> I have kind of wild card masks in my type definition, so b-tree won't work.
> But still, do I need to define b-tree index as such for "order by" to work ?
> Perhaps gist should be expanded so it would take care of "order by" and
> "distinct" ?

look contrib/ltree for reference (sql/ltree.sql)

>
> Thanks.
>
>
>

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

Grzegorz Jaskiewicz <gj(at)pointblue(dot)com(dot)pl> writes:

> Hi folks
>
> I have all operators required for b-tree, gist up here, and gist index
> defined. But still "order by custom_type" won't work.

I think you need to create an "operator class" for ORDER BY to work. Someone
else may answer with more details.

> I have kind of wild card masks in my type definition, so b-tree won't work.
> But still, do I need to define b-tree index as such for "order by" to work ?
> Perhaps gist should be expanded so it would take care of "order by" and
> "distinct" ?

This I don't understand. If ORDER BY will work then b-tree indexes will work
too. If your type is such that b-tree indexes don't make sense then neither
does ORDER BY.

--
greg

Grzegorz Jaskiewicz <gj(at)pointblue(dot)com(dot)pl> writes:
> I have all operators required for b-tree, gist up here, and gist
> index defined. But still "order by custom_type" won't work.

Define "won't work" ... what happens?

> I have kind of wild card masks in my type definition, so b-tree won't
> work. But still, do I need to define b-tree index as such for "order
> by" to work ?

You don't need an index, but a b-tree operator class is a good idea.
Still, it should be possible to sort with only a "<" operator --- at
the moment anyway. (I've been thinking about some ideas that would
effectively require a b-tree opclass to do sorting, so this might not
still be true in 8.2 ...)

regards, tom lane

On 2005-11-18, at 22:53, Tom Lane wrote:

> Grzegorz Jaskiewicz <gj(at)pointblue(dot)com(dot)pl> writes:
>> I have all operators required for b-tree, gist up here, and gist
>> index defined. But still "order by custom_type" won't work.
>
> Define "won't work" ... what happens?
>

Wildcards cause things not to work as they should

consider everything in [] brackets to be a possible choice and those
three:

a = 1.2.3.4
b = 1.[2,3].3.4
c = 1.3.3.4

a = b, b = c, but a <> c, I was told that because of that btree won't
work on my type. (on irc, that was AndrewSN as I recall).

> You don't need an index, but a b-tree operator class is a good idea.
> Still, it should be possible to sort with only a "<" operator --- at
> the moment anyway. (I've been thinking about some ideas that would
> effectively require a b-tree opclass to do sorting, so this might not
> still be true in 8.2 ...)

I do have all operators required for btree, no operator class
defined, every single operator. Btree requires some function apart
from operators, this one is not defined, but I do have = operator as
well.

--
GJ

"If we knew what we were doing, it wouldn't be called Research, would
it?" - AE

Grzegorz Jaskiewicz <gj(at)pointblue(dot)com(dot)pl> writes:
> Wildcards cause things not to work as they should

> consider everything in [] brackets to be a possible choice and those
> three:

> a = 1.2.3.4
> b = 1.[2,3].3.4
> c = 1.3.3.4

> a = b, b = c, but a <> c, I was told that because of that btree won't
> work on my type. (on irc, that was AndrewSN as I recall).

Well, neither will sorting then. If you can define a consistent sort
order, btree will work; if you can't, then you can't sort either.
AFAICS, with rules like the above you can't define a consistent <
operator.

regards, tom lane

On 2005-11-19, Grzegorz Jaskiewicz <gj(at)pointblue(dot)com(dot)pl> wrote:
> Wildcards cause things not to work as they should
>
> consider everything in [] brackets to be a possible choice and those
> three:
>
> a = 1.2.3.4
> b = 1.[2,3].3.4
> c = 1.3.3.4
>
> a = b, b = c, but a <> c, I was told that because of that btree won't
> work on my type. (on irc, that was AndrewSN as I recall).

Probably. But nothing stops you defining equality and ordering operators
that _do_ work for btree, and hence sorting, it's just that those operators
won't be any use for the matching semantics.

It's clear that for your data type that there is a concept of "equality"
in which all three of your values a,b,c above are unequal. My advice would
be (and I'm sure I suggested this at the time) that you reserve the '='
operator for a true equality operation, and use some other operator such as
~ or @ for the "matches" semantics that you want for your application.
Having an intransitive '=' operator violates the POLA, even if it doesn't
actively break anything otherwise (I have no idea if it does).

> I do have all operators required for btree, no operator class
> defined, every single operator. Btree requires some function apart
> from operators, this one is not defined, but I do have = operator as
> well.

You still don't seem to understand that what btree requires is not an
operator _called_ '=', but an operator with the logical semantics of
"equality". That operator can be called anything you please (it doesn't
have to have the name '=').

Sorting doesn't need an equality operator, since it can fabricate one if
given a suitable < operator, i.e. one that constitutes a strict weak
ordering over the elements to be sorted; it can rely on the fact that
NOT(a < b) AND NOT(b < a) implies that a and b are equivalent for sorting
purposes. (The requirement that < constitute a strict weak ordering is
enough to ensure that this is an equivalence relation, and therefore
transitive; if < does not meet this requirement then sorting may give wrong
answers, loop forever, or possibly crash.)

--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services

aye aye Sir.

--
GJ

"If we knew what we were doing, it wouldn't be called Research, would
it?" - AE

Take the query.

select a,b from dupa where b::text in (select b::text from dupa group by
b::text having count(b) > 2);

This is acceptable to create a unique constraint, however, we cannot mark
the column unique, without defining btree operators, which clearly are not
possible for sorting. Is there any way to base the operators based on the
text representation of the type for strict equality (not to be confused with
same or equivilent) and thus use that not as an ordering method, but as a
simple equality for uniqueness.

Kevin McArthur

----- Original Message -----
From: "Andrew - Supernews" <andrew+nonews(at)supernews(dot)com>
To: <pgsql-hackers(at)postgresql(dot)org>
Sent: Saturday, November 19, 2005 10:54 PM
Subject: Re: [HACKERS] order by, for custom types

> On 2005-11-19, Grzegorz Jaskiewicz <gj(at)pointblue(dot)com(dot)pl> wrote:
>> Wildcards cause things not to work as they should
>>
>> consider everything in [] brackets to be a possible choice and those
>> three:
>>
>> a = 1.2.3.4
>> b = 1.[2,3].3.4
>> c = 1.3.3.4
>>
>> a = b, b = c, but a <> c, I was told that because of that btree won't
>> work on my type. (on irc, that was AndrewSN as I recall).
>
> Probably. But nothing stops you defining equality and ordering operators
> that _do_ work for btree, and hence sorting, it's just that those
> operators
> won't be any use for the matching semantics.
>
> It's clear that for your data type that there is a concept of "equality"
> in which all three of your values a,b,c above are unequal. My advice would
> be (and I'm sure I suggested this at the time) that you reserve the '='
> operator for a true equality operation, and use some other operator such
> as
> ~ or @ for the "matches" semantics that you want for your application.
> Having an intransitive '=' operator violates the POLA, even if it doesn't
> actively break anything otherwise (I have no idea if it does).
>
>> I do have all operators required for btree, no operator class
>> defined, every single operator. Btree requires some function apart
>> from operators, this one is not defined, but I do have = operator as
>> well.
>
> You still don't seem to understand that what btree requires is not an
> operator _called_ '=', but an operator with the logical semantics of
> "equality". That operator can be called anything you please (it doesn't
> have to have the name '=').
>
> Sorting doesn't need an equality operator, since it can fabricate one if
> given a suitable < operator, i.e. one that constitutes a strict weak
> ordering over the elements to be sorted; it can rely on the fact that
> NOT(a < b) AND NOT(b < a) implies that a and b are equivalent for sorting
> purposes. (The requirement that < constitute a strict weak ordering is
> enough to ensure that this is an equivalence relation, and therefore
> transitive; if < does not meet this requirement then sorting may give
> wrong
> answers, loop forever, or possibly crash.)
>
> --
> Andrew, Supernews
> http://www.supernews.com - individual and corporate NNTP services
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

"Kevin McArthur" <Kevin(at)StormTide(dot)ca> writes:
> This is acceptable to create a unique constraint, however, we cannot mark
> the column unique, without defining btree operators, which clearly are not
> possible for sorting. Is there any way to base the operators based on the
> text representation of the type for strict equality (not to be confused with
> same or equivilent) and thus use that not as an ordering method, but as a
> simple equality for uniqueness.

Translation: you do know how to define a sortable order (ie, generate
the text version and compare); you're just too lazy to create the
operators to do it ...

regards, tom lane

On 2005-11-22, at 15:45, Tom Lane wrote:

> "Kevin McArthur" <Kevin(at)StormTide(dot)ca> writes:
>> This is acceptable to create a unique constraint, however, we
>> cannot mark
>> the column unique, without defining btree operators, which clearly
>> are not
>> possible for sorting. Is there any way to base the operators based
>> on the
>> text representation of the type for strict equality (not to be
>> confused with
>> same or equivilent) and thus use that not as an ordering method,
>> but as a
>> simple equality for uniqueness.
>
> Translation: you do know how to define a sortable order (ie, generate
> the text version and compare); you're just too lazy to create the
> operators to do it ...
We do have WORKING < , > , etc operators, and ::text cast already.
Thing is, can I have btree and gist indexes at the same time ?

--
GJ

"If we knew what we were doing, it wouldn't be called Research, would
it?" - AE

On Tue, Nov 22, 2005 at 04:24:21PM +0100, Grzegorz Jaskiewicz wrote:
> >Translation: you do know how to define a sortable order (ie, generate
> >the text version and compare); you're just too lazy to create the
> >operators to do it ...
> We do have WORKING < , > , etc operators, and ::text cast already.
> Thing is, can I have btree and gist indexes at the same time ?

Sure, did you look at the ltree example someone pointed you to?

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)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.

On Tue, 22 Nov 2005, Grzegorz Jaskiewicz wrote:
> Thing is, can I have btree and gist indexes at the same time ?

no, we have contrib/btree_gist for this

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

On 2005-11-22, at 16:39, Martijn van Oosterhout wrote:

> On Tue, Nov 22, 2005 at 04:24:21PM +0100, Grzegorz Jaskiewicz wrote:
>>> Translation: you do know how to define a sortable order (ie,
>>> generate
>>> the text version and compare); you're just too lazy to create the
>>> operators to do it ...
>> We do have WORKING < , > , etc operators, and ::text cast already.
>> Thing is, can I have btree and gist indexes at the same time ?
>
> Sure, did you look at the ltree example someone pointed you to?
>
> Have a nice day,
Ok, I hacked btree for my type, and surely I can have both btree and
gist at the same time on the same column.
/me is now going to have a look on btree_gist.

Thanks folks.

--
GJ

"If we knew what we were doing, it wouldn't be called Research, would
it?" - AE

On Tue, Nov 22, 2005 at 05:14:35PM +0100, Grzegorz Jaskiewicz wrote:
> Ok, I hacked btree for my type, and surely I can have both btree and
> gist at the same time on the same column.
> /me is now going to have a look on btree_gist.

You don't actually have to have a btree defined on your column for
ORDER BY to work, you just need to define a btree OPERATOR CLASS so
that PostgreSQL knows what you mean by "ORDER BY".

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)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.

On 2005-11-22, at 17:17, Martijn van Oosterhout wrote:

> On Tue, Nov 22, 2005 at 05:14:35PM +0100, Grzegorz Jaskiewicz wrote:
>> Ok, I hacked btree for my type, and surely I can have both btree and
>> gist at the same time on the same column.
>> /me is now going to have a look on btree_gist.
>
> You don't actually have to have a btree defined on your column for
> ORDER BY to work, you just need to define a btree OPERATOR CLASS so
> that PostgreSQL knows what you mean by "ORDER BY".
yep, I know. Thanks.
Anyhow, I wanted to see if having two indexes will make it faster/
slower. and if it is possible in first place.

--
GJ

"If we knew what we were doing, it wouldn't be called Research, would
it?" - AE