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;

Wed, 19 Nov 2003 11:44:16 +0100
strk <strk@keybit.net>
PG7.4 ordering operator
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

Thu, 20 Nov 2003 00:59:56 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: PG7.4 ordering operator 
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-DEPENDENCIES
> 
> 			regards, 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;

Thu, 20 Nov 2003 11:23:09 +0100
strk <strk@keybit.net>
Re: PG7.4 ordering operator
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

Thu, 20 Nov 2003 09:44:17 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: PG7.4 ordering operator 