Thread: Creating a VIEW with a POINT column
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
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
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
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
Jan Urbański <j.urbanski@students.mimuw.edu.pl> 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
Tom Lane wrote: <blockquote cite="mid:25421.1214438480@sss.pgh.pa.us" type="cite"><pre wrap="">Jan Urbański <a class="moz-txt-link-rfc2396E" href="mailto:j.urbanski@students.mimuw.edu.pl"><j.urbanski@students.mimuw.edu.pl></a>writes: </pre><blockquote type="cite"><prewrap="">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. </pre></blockquote><pre wrap=""> 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... </pre></blockquote><br /> I agree - a byte-wise comparisonof the internal encoding might be inadequate (compare "0.0e+1" to "0.0e+2" is "not equal" for instance?). If theposter 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 thewrong assumption, and I really like that PostgreSQL will refuse to do things rather than silently continue to do whatmay be the wrong thing (MySQL silent truncation when assigning into a varchar(8) for example).<br /><br /> The problemhere seems to that "point" should have an equality operator?<br /><br /> Cheers,<br /> mark<br /><br /><pre class="moz-signature"cols="72">-- Mark Mielke <a class="moz-txt-link-rfc2396E" href="mailto:mark@mielke.cc"><mark@mielke.cc></a> </pre>
Mark Mielke <mark@mark.mielke.cc> 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
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
<p><font size="2">Nick wrote:<br /><br /> > I have a VIEW that consists of two tables, of which contain a POINT<br />> column. When trying to select from the view I get an error...<br /> ><br /> > ERROR: could not identify anordering operator for type point<br /> > HINT: Use an explicit ordering operator or modify the query.<br /> ><br/> > Any suggestions??? -Nick<br /> ><br /><br /> I'm a lurker on this list (came for the 8.3 release, stayedfor the delightful banter), but I have noticed that seems to be a real issue, at least for the moment.<br /><br />Not trying to be snotty, but perhaps using postGIS <<a href="http://postgis.refractions.net/">http://postgis.refractions.net/</a>>would be a suitable alternate ? It does requireadmin rights to install but the point does have an equality op, GIST indexing and is reasonably light-weight in diskspace.<br /><br /> Ok, you probably already rejected this for good reason ... back to the real thread.<br /><br /> Apologiesfor the signage below ...<br /><br /> Greg Williamson<br /> Senior DBA<br /> DigitalGlobe<br /><br /> ConfidentialityNotice: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) andmay contain confidential and privileged information and must be protected in accordance with those provisions. Any unauthorizedreview, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contactthe sender by reply e-mail and destroy all copies of the original message.<br /><br /> (My corporate masters mademe say this.)<br /></font>
Jan Urbański <j.urbanski@students.mimuw.edu.pl> 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
Tom Lane wrote: > Jan Urbański <j.urbanski@students.mimuw.edu.pl> 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
Jan Urbański <j.urbanski@students.mimuw.edu.pl> 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
Jan Urbański <j.urbanski@students.mimuw.edu.pl> 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
Tom Lane wrote: > Jan Urbański <j.urbanski@students.mimuw.edu.pl> 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
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