[Bucardo-general] "ERROR: 42883: could not identify an ordering operator for type polygon"

[Joshua Tolley]

On Tue, Apr 28, 2009 at 11:22:28AM -0400, Katz, Lawrence wrote:
>    I'm trying to setup Bucardo to do a master-master replication between two
>    PostgreSQL servers.  However, it looks like when it tries to execute a
>    query involving DISTINCT and a column with a data type of "polygon", the
>    following error is thrown by PostgreSQL:
> 
>    ERROR:  42883: could not identify an ordering operator for type polygon
> 
>    I assume a default operator class for polygon needs to be defined using
>    "CREATE OPERATOR CLASS"
>    (http://www.postgresql.org/docs/8.1/static/sql-createopclass.html).  Does
>    anyone have an example of an appropriate operator class definition for
>    this data type?

See
http://www.postgresql.org/docs/7.4/interactive/xindex.html#XINDEX-OPCLASS-DEPENDENCIES
for a description of why this happens. In short, in order to do
operations like DISTINCT or GROUP BY, PostgreSQL needs to be able to
sort the values one-dimensionally, like numbers on a number line, and
uses the operator class information to find the operators that will
make that happen. Polygons don't really have an obvious, meaningful
one-dimensional ordering, so none of the necessary operators exist.

An interesting thought, though, might be that for cases like DISTINCT
and GROUP BY, the ordering doesn't have to be particularly meaningful,
as long as it's consistent -- i.e. equivalent polygons always end up in
the same order in relation to each other. An intelligent equality
operator paired with simple operators to, say, sort polygons based on
the number of vertices, might suffice.

All that doesn't help you much, unless you feel like writing such a
thing. I can't help you to find something pre-built.

-- 
Josh Tolley  josh at endpoint.com  801-485-0203
End Point Corporation

-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 197 bytes
Desc: Digital signature
Url : https://mail.endcrypt.com/pipermail/bucardo-general/attachments/20090428/04c74496/attachment.bin