PG7.4 ordering operator
Testing postgis support in PG7.4 (2003-11-11)
I've encountered to this problem:
ERROR: could not identify an ordering operator for type geometry
HINT: Use an explicit ordering operator or modify the query.
Whenever I issue one of these commands:
gis=# select the_geom from table1 UNION select the_geom from table2;
gis=# select DISTINCT the_geom from table1;
gis=# select the_geom from table1 ORDER BY the_geom;
Operators '<', '>', '=' are available:
oprname | leftoperand | rightoperand
---------+-------------+--------------
<< | geometry | geometry
&< | geometry | geometry
&& | geometry | geometry
&> | geometry | geometry
| geometry | geometry
~= | geometry | geometry
@ | geometry | geometry
~ | geometry | geometry
= | geometry | geometry
< | geometry | geometry
| geometry | geometry
(11 rows)
Previous PG versions does not show this problem.
Any hint on what might be missing ?
--strk;
strk <strk@keybit.net> writes:
Testing postgis support in PG7.4 (2003-11-11)
I've encountered to this problem:
ERROR: could not identify an ordering operator for type geometry
Previous PG versions does not show this problem.
Any hint on what might be missing ?
A default btree operator class for type geometry. PG 7.4 no longer
uses assumptions about operator names to determine sorting/grouping
behavior. If you have some operators that provide a scalar sort
ordering on your datatype, then make a btree opclass to show that.
See
http://www.postgresql.org/docs/7.4/static/xindex.html#XINDEX-OPCLASS-DEPENDENCIES
regards, tom lane
tgl wrote:
strk <strk@keybit.net> writes:
Testing postgis support in PG7.4 (2003-11-11)
I've encountered to this problem:
ERROR: could not identify an ordering operator for type geometry
Previous PG versions does not show this problem.
Any hint on what might be missing ?A default btree operator class for type geometry. PG 7.4 no longer
uses assumptions about operator names to determine sorting/grouping
behavior. If you have some operators that provide a scalar sort
ordering on your datatype, then make a btree opclass to show that.
See
http://www.postgresql.org/docs/7.4/static/xindex.html#XINDEX-OPCLASS-DEPENDENCIESregards, tom lane
Thanks for the answer, I've one more question:
I've provided a default btree operator class but I'm often
going out of memory when using DISTINCT or UNION clauses.
How can I reduce memory usage in these cases ?
Since passed argument are TOASTED, but I use only a small
initial portion if them to make the computation, can I
avoid DETOASTING them and still reach that initial part ?
The information I need is stored at offset 40 from detoasted data
and is 6doubles long. I cannot find TOAST documentation.
thanks.
--strk;
strk <strk@keybit.net> writes:
I've provided a default btree operator class but I'm often
going out of memory when using DISTINCT or UNION clauses.
How can I reduce memory usage in these cases ?
Since passed argument are TOASTED, but I use only a small
initial portion if them to make the computation, can I
avoid DETOASTING them and still reach that initial part ?
No, I don't think so, but see PG_FREE_IF_COPY. Operators used
in btree indexes are expected not to leak memory.
regards, tom lane