pg_upgrade error: Operator is not unique

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

pg_upgrade error: Operator is not unique

Daniel Baston
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
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: pg_upgrade error: Operator is not unique

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

_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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