SEO

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




---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Grzegorz Jaskiewicz <gj@pointblue.com.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


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Grzegorz Jaskiewicz <gj@pointblue.com.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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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

> Grzegorz Jaskiewicz <gj@pointblue.com.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




---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Grzegorz Jaskiewicz <gj@pointblue.com.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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

On 2005-11-19, Grzegorz Jaskiewicz <gj@pointblue.com.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




---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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@supernews.com>
To: <pgsql-hackers@postgresql.org>
Sent: Saturday, November 19, 2005 10:54 PM
Subject: Re: [HACKERS] order by, for custom types


> On 2005-11-19, Grzegorz Jaskiewicz <gj@pointblue.com.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
>


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

"Kevin McArthur" <Kevin@StormTide.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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly