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

[Daniel Baston]

Hi Regina,

Indeed, this view is not in the public schema, and it looks to be a
search path issue.  The following very simple view is unrestorable:

CREATE VIEW junk.bad AS
SELECT 'POINT (0 3)'::geometry IS DISTINCT FROM 'POINT (3 0)'::geometry;

This yields the same error:

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 563; 1259 1508877
VIEW bad dbaston
pg_restore: [archiver (db)] could not execute query: ERROR:  operator
is not unique: public.geometry = public.geometry
LINE 2: ...0000000000000000000000000000840'::public.geometry IS DISTINC...
                                                             ^
HINT:  Could not choose a best candidate operator. You might need to
add explicit type casts.

This seems to be a known issue with Postgres (#11617), but the thread
describing the bug hasn't seen any activity in two years.
(https://www.postgresql.org/message-id/20141009200031.25464.53769%40wrigleys.postgresql.org)

Thanks,
Dan

On Mon, Jan 9, 2017 at 10:14 PM, Regina Obe <lr at pcorp.us> wrote:
> 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
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/postgis-users