Re: [HACKERS] Creating a VIEW with a POINT column

2008-06-26 Thread Dimitri Fontaine
Le jeudi 26 juin 2008, Tom Lane a écrit :
 Yeah.  The GROUP BY case is even more annoying, because we *have* the
 planner/executor infrastructure to do it via hashing; but the parser
 barfs immediately if there is not btree opclass support for the type.
 I'm not sure how to fix the parser and the parsetree representation
 to be agnostic about hash versus sort implementations --- any thoughts?

Would it be possible to add some semantics to the operator itself?
I'm thinking about indicating that an operator is the equality one without 
resorting to OPCLASS and while at it adding the notion of transitivity to 
operators (which you'd like to abuse for some joins conditions iirc).

The CREATE OPERATOR =(type, type) (... EQUALITY ...) would give the 
information to PostgreSQL and its planner. I'm not sure it current operator 
catalog allows us to have a unique constraint for an equality operator for a 
given couple of (LEFTARG, RIGHARG), though.

Would this help?
-- 
dim


signature.asc
Description: This is a digitally signed message part.


Re: [HACKERS] Creating a VIEW with a POINT column

2008-06-25 Thread Jan Urbański

Nick wrote:

I have a VIEW that consists of two tables, of which contain a POINT
column. When trying to select from the view I get an error...

ERROR:  could not identify an ordering operator for type point
HINT:  Use an explicit ordering operator or modify the query.

Any suggestions??? -Nick


Does your view per chance do ORDER BY point_column ? You should then 
do as told and use an explicit ordering operator.

See
http://www.postgresql.org/docs/8.3/static/sql-select.html#SQL-ORDERBY.

To get better feedback you really should post your view definition (and 
the definitions of those two underlying tables).


Cheers,
Jan

--
Jan Urbanski
GPG key ID: E583D7D2

ouden estin

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Creating a VIEW with a POINT column

2008-06-25 Thread Nick
Nope, im not ordering by the POINT column. Heres an example...

CREATE TABLE table1 (
title character varying,
sorter integer,
xy point
);

CREATE TABLE table2 (
title character varying,
sorter integer,
xy point
);

INSERT INTO table1 VALUES ('one', 1, '(1,1)');
INSERT INTO table1 VALUES ('two', 2, '(2,2)');
INSERT INTO table1 VALUES ('three', 3, '(3,3)');
INSERT INTO table2 VALUES ('four', 4, '(4,4)');
INSERT INTO table2 VALUES ('five', 5, '(5,5)');
INSERT INTO table2 VALUES ('six', 6, '(6,6)');

CREATE VIEW myview AS
SELECT table1.title, table1.sorter, table1.xy FROM table1 UNION
SELECT table2.title, table2.sorter, table2.xy FROM table2;

SELECT title FROM myview ORDER BY sorter;

ERROR:  could not identify an ordering operator for type point
HINT:  Use an explicit ordering operator or modify the query.

In statement:
SELECT title FROM myview ORDER BY sorter

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Creating a VIEW with a POINT column

2008-06-25 Thread Jan Urbański

Nick wrote:

Nope, im not ordering by the POINT column. Heres an example...

CREATE VIEW myview AS
SELECT table1.title, table1.sorter, table1.xy FROM table1 UNION
SELECT table2.title, table2.sorter, table2.xy FROM table2;


Hmm, the error seems to be coming from UNION. It's because Postgres 
implements UNION by sorting both result sets merging them together.

Sample queries that also fail:

SELECT * FROM myview;
SELECT DISTINCT * FROM table1;
SELECT title, sorter, xy FROM table1 GROUP BY title, sorter, xy;

All three try to sort the table first, and as there's no comparision 
operator for the POINT datatype, they fail. Which seems to be wrong - if 
there is no comparision operator, you still can do DISTINCT, only less 
efficiently.


The quick solution I'd propose is replacing UNION with UNION ALL. This 
will not throw away duplicate entries present in both table1 and table2, 
but if you can live with that, it will work. Remeber though, it changes 
the semantic of that view, so think carefuly before doing that.


I guess some senior hacker should confirm, but I believe this is a bug.

Jan

--
Jan Urbanski
GPG key ID: E583D7D2

ouden estin

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Creating a VIEW with a POINT column

2008-06-25 Thread Tom Lane
=?UTF-8?B?SmFuIFVyYmHFhHNraQ==?= [EMAIL PROTECTED] writes:
 All three try to sort the table first, and as there's no comparision 
 operator for the POINT datatype, they fail. Which seems to be wrong - if 
 there is no comparision operator, you still can do DISTINCT, only less 
 efficiently.

Type point has no btree opclass, no hash opclass, and not even an
operator named = (it looks like the functionality is named ~=
for some odd reason).  I'd be interested to hear either a proposal of
a principled way to define DISTINCT, or a way to implement it that
was better than comparing every element to every other element...

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Creating a VIEW with a POINT column

2008-06-25 Thread Mark Mielke

Tom Lane wrote:

=?UTF-8?B?SmFuIFVyYmHFhHNraQ==?= [EMAIL PROTECTED] writes:
  
All three try to sort the table first, and as there's no comparision 
operator for the POINT datatype, they fail. Which seems to be wrong - if 
there is no comparision operator, you still can do DISTINCT, only less 
efficiently.



Type point has no btree opclass, no hash opclass, and not even an
operator named = (it looks like the functionality is named ~=
for some odd reason).  I'd be interested to hear either a proposal of
a principled way to define DISTINCT, or a way to implement it that
was better than comparing every element to every other element...
  


I agree - a byte-wise comparison of the internal encoding might be 
inadequate (compare 0.0e+1 to 0.0e+2 is not equal for instance?). 
If the poster is referring to a translation to string before comparing, 
this could face similar issue. What if it's not a point but a 
fraction - does 2/4 = 1/2? With an operator implementing =, 
making any assumption may be making the wrong assumption, and I really 
like that PostgreSQL will refuse to do things rather than silently 
continue to do what may be the wrong thing (MySQL silent truncation when 
assigning into a varchar(8) for example).


The problem here seems to that point should have an equality operator?

Cheers,
mark

--
Mark Mielke [EMAIL PROTECTED]



Re: [HACKERS] Creating a VIEW with a POINT column

2008-06-25 Thread Tom Lane
Mark Mielke [EMAIL PROTECTED] writes:
 The problem here seems to that point should have an equality operator?

For starters ;-).  The current implementation of UNION requires it to
have a complete btree opclass.  In principle I suppose we could
implement hash-based DISTINCT, which would require only a hash opclass,
but that isn't there either.

Note that the only way that the system knows that an operator has
the semantics of equality is for it to be the equality member of
a btree or hash opclass; there isn't any other representation of
operator semantics in Postgres.  So the opclasses not only provide
necessary execution infrastructure, but also the justification
for using a particular operator to define DISTINCT-ness.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers



Re: [HACKERS] Creating a VIEW with a POINT column

2008-06-25 Thread Jan Urbański

Mark Mielke wrote:

Tom Lane wrote:

Type point has no btree opclass, no hash opclass, and not even an
operator named = (it looks like the functionality is named ~=
for some odd reason).  I'd be interested to hear either a proposal of
a principled way to define DISTINCT, or a way to implement it that
was better than comparing every element to every other element...


The way I see it there's nothing wrong with the definition of DISTINCT 
and for types that can't be compared there is no way of calculating 
distinct values other than comparing every element to every other.
My point is that it is theoretically possible to do DISTINCT with only a 
equality operator. Sure, it's impractical, but it's a valid operation. 
If you can tell which elements are equal, you can take the largest 
subset of elements, among which no two are equal.
The least that can be done is improve the error message. Maybe something 
like: The query required to sort elements of type foo to calculate 
the result efficiently, but there is no ordering operator for type 
foo would do. And document, that GROUP BY, DISTINCT and UNION fail on 
types that can't be sorted.


I agree - a byte-wise comparison of the internal encoding might be 
inadequate (compare 0.0e+1 to 0.0e+2 is not equal for instance?). 
If the poster is referring to a translation to string before comparing, 



The problem here seems to that point should have an equality operator?


I think it has (=~, as Tom pointed out). The real problem is: should 
there be code to do GROUP BY / DISTINCT when there are no btree or hash 
opclasses, or should it be considered an error, because doing it would 
take very long for larger result sets?


Cheers,
Jan

--
Jan Urbanski
GPG key ID: E583D7D2

ouden estin

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Creating a VIEW with a POINT column

2008-06-25 Thread Gregory Williamson
Nick wrote:

 I have a VIEW that consists of two tables, of which contain a POINT
 column. When trying to select from the view I get an error...
 
 ERROR:  could not identify an ordering operator for type point
 HINT:  Use an explicit ordering operator or modify the query.
 
 Any suggestions??? -Nick
 

I'm a lurker on this list (came for the 8.3 release, stayed for the delightful 
banter), but I have noticed that seems to be a real issue, at least for the 
moment.

Not trying to be snotty, but perhaps using postGIS 
http://postgis.refractions.net/ would be a suitable alternate ? It does 
require admin rights to install but the point does have an equality op, GIST 
indexing and is reasonably light-weight in disk space.

Ok, you probably already rejected this for good reason ... back to the real 
thread.

Apologies for the signage below ...

Greg Williamson
Senior DBA
DigitalGlobe

Confidentiality Notice: This e-mail message, including any attachments, is for 
the sole use of the intended recipient(s) and may contain confidential and 
privileged information and must be protected in accordance with those 
provisions. Any unauthorized review, use, disclosure or distribution is 
prohibited. If you are not the intended recipient, please contact the sender by 
reply e-mail and destroy all copies of the original message.

(My corporate masters made me say this.)


Re: [HACKERS] Creating a VIEW with a POINT column

2008-06-25 Thread Tom Lane
=?UTF-8?B?SmFuIFVyYmHFhHNraQ==?= [EMAIL PROTECTED] writes:
 Tom Lane wrote:
 Type point has no btree opclass, no hash opclass, and not even an
 operator named = (it looks like the functionality is named ~=
 for some odd reason).  I'd be interested to hear either a proposal of
 a principled way to define DISTINCT, or a way to implement it that
 was better than comparing every element to every other element...

 The way I see it there's nothing wrong with the definition of DISTINCT 
 and for types that can't be compared there is no way of calculating 
 distinct values other than comparing every element to every other.

for types that can't be compared?  Do you not see the logical
disconnect in that sentence?

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Creating a VIEW with a POINT column

2008-06-25 Thread Jan Urbański

Tom Lane wrote:

=?UTF-8?B?SmFuIFVyYmHFhHNraQ==?= [EMAIL PROTECTED] writes:

Tom Lane wrote:

Type point has no btree opclass, no hash opclass, and not even an
operator named = (it looks like the functionality is named ~=
for some odd reason).  I'd be interested to hear either a proposal of
a principled way to define DISTINCT, or a way to implement it that
was better than comparing every element to every other element...


The way I see it there's nothing wrong with the definition of DISTINCT 
and for types that can't be compared there is no way of calculating 
distinct values other than comparing every element to every other.


for types that can't be compared?  Do you not see the logical
disconnect in that sentence?


OK, there might have been a mental shortcut there. Can't be compared 
was supposed to mean can't decide whether one value of that type is 
bigger than another. Doing DISTINCT without an equality operator is 
nonsense. Doing it without a comparision operator is only very slow.


--
Jan Urbanski
GPG key ID: E583D7D2

ouden estin

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Creating a VIEW with a POINT column

2008-06-25 Thread Tom Lane
=?UTF-8?B?SmFuIFVyYmHFhHNraQ==?= [EMAIL PROTECTED] writes:
 OK, there might have been a mental shortcut there. Can't be compared 
 was supposed to mean can't decide whether one value of that type is 
 bigger than another. Doing DISTINCT without an equality operator is 
 nonsense. Doing it without a comparision operator is only very slow.

Well, you're still missing my point, which is how do you decide which
operator is equality?  It was already pointed out upthread that
ignoring the type's operators and using bitwise comparison is a pretty
sucky alternative.  The only infrastructure in Postgres that can
identify which operators have which semantics is index opclasses.

I see two possible TODO items in this discussion.  One is that type
point is sorely lacking in opclass support.  The other is that it
might be interesting to support DISTINCT in cases where only a hash
opclass, not a btree opclass, is available --- which would lead to
a hash-aggregation-like implementation instead of sort-and-uniq.
The value as far as type point is concerned is that you'd not have to
invent some arbitrary linear sort ordering for points.

The idea of supporting DISTINCT with neither type of opclass available
seems to me to be indefensible on *both* semantics and performance
grounds.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Creating a VIEW with a POINT column

2008-06-25 Thread Tom Lane
=?UTF-8?B?SmFuIFVyYmHFhHNraQ==?= [EMAIL PROTECTED] writes:
 Same thing for GROUP BY.

Yeah.  The GROUP BY case is even more annoying, because we *have* the
planner/executor infrastructure to do it via hashing; but the parser
barfs immediately if there is not btree opclass support for the type.
I'm not sure how to fix the parser and the parsetree representation
to be agnostic about hash versus sort implementations --- any thoughts?

 ... One last remark: unless something is done about 
 it in 8.4, maybe it is worthwhile to change the error message (which 
 clearly confused Nick) and add some documentation about why you can't 
 use DISTINCT with types without a btree index opclass?

IIRC, the error message is correct for some other cases, so it might be
hard to fix it without taking two steps backward overall.  But feel free
to take a look if you want to.  Nuthin wrong with more documentation
either ...

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Creating a VIEW with a POINT column

2008-06-25 Thread Jan Urbański

Tom Lane wrote:

=?UTF-8?B?SmFuIFVyYmHFhHNraQ==?= [EMAIL PROTECTED] writes:
OK, there might have been a mental shortcut there. Can't be compared 
was supposed to mean can't decide whether one value of that type is 
bigger than another. Doing DISTINCT without an equality operator is 
nonsense. Doing it without a comparision operator is only very slow.


Well, you're still missing my point, which is how do you decide which
operator is equality?  It was already pointed out upthread that
ignoring the type's operators and using bitwise comparison is a pretty
sucky alternative.  The only infrastructure in Postgres that can
identify which operators have which semantics is index opclasses.


All right, I get it. You get the equality operator from the index 
opclass for the type, I didn't understand fully how it worked.



I see two possible TODO items in this discussion.  One is that type
point is sorely lacking in opclass support.  The other is that it
might be interesting to support DISTINCT in cases where only a hash
opclass, not a btree opclass, is available --- which would lead to
a hash-aggregation-like implementation instead of sort-and-uniq.


Same thing for GROUP BY. One last remark: unless something is done about 
it in 8.4, maybe it is worthwhile to change the error message (which 
clearly confused Nick) and add some documentation about why you can't 
use DISTINCT with types without a btree index opclass?


--
Jan Urbanski
GPG key ID: E583D7D2

ouden estin

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers