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 <[email protected]> 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:[email protected]] On Behalf
> Of Daniel Baston
> Sent: Monday, January 09, 2017 2:26 PM
> To: PostGIS Users Discussion <[email protected]>
> 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
> [email protected]
> http://lists.osgeo.org/mailman/listinfo/postgis-users
>
> _______________________________________________
> postgis-users mailing list
> [email protected]
> http://lists.osgeo.org/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://lists.osgeo.org/mailman/listinfo/postgis-users
