[postgis-users] pg_upgrade error: Operator is not unique

[Regina Obe]

Dan,

Which schema is your view in?

Curious I found this same complaint a while back - 

https://lists.osgeo.org/pipermail/postgis-users/2012-March/032975.html

Well that person had same issue with IS DISTINCT.. and was able to fix by adding the public schema to search_path.

So if it's a different schema I'm wondering if postgres is not smart enough to schema qualify the IS DISTINCT .. actually I don't know how to schema qualify IS DISTINCT . Perhaps there is no way like you can with operators.

So I'm guessing what might be happening is pg_restore, since it changes search_path to only have pg_catalog and schema of the view, i
t doesn't have public in search_path.

So what it's finding is two suboptimal operators that geometry can autocast to -- probably like one of those built in PostgreSQL box types, and it's find more than one of those so doesn't know which to use, cause they both are equally sucky for geometry.




-----Original Message-----
From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Daniel Baston
Sent: Monday, January 09, 2017 2:26 PM
To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
Subject: [postgis-users] pg_upgrade error: Operator is not unique

Hi All,

I'm trying to use pg_upgrade to upgrade a large database from Postgres
9.5 to 9.6, both with PostGIS 2.3.1.

The procedure works well except for an error on a single view:

pg_restore: [archiver (db)] could not execute query: ERROR:  operator is not unique: public.geometry = public.geometry LINE 53: ...rg" ON (((NOT ("hospital_points"."hospital_point" IS DISTINC...
                                                              ^
HINT:  Could not choose a best candidate operator. You might need to add explicit type casts.
    Command was:
-- For binary upgrade, must preserve pg_type oid SELECT pg_catalog.binary_upgrade_set_next_pg_type_oid('205493750'::pg_cata...

If I remove the view from the 9.5 database, the entire process works correctly.  I'm then able to create the view manually in 9.6 after the restore finishes.

I've verified on both the 9.5 and 9.6 databases that the = operator is in fact unique for geometry using

SELECT * FROM pg_operator WHERE oprname='=' AND oprleft=oprright AND oprleft=(SELECT oid FROM pg_type WHERE typname='geometry')

Anyone else run into this before?

Thanks,
Dan
_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users