[postgis-tickets] [PostGIS] #4257: operator does not exist: gidx public.&& geography when upgrading from 9.4 postgis 2.4 to pg 11 3.0

[PostGIS]

#4257: operator does not exist: gidx public.&& geography when upgrading from 9.4
postgis 2.4 to pg 11 3.0
-----------------------------------+---------------------------
 Reporter:  robe                   |      Owner:  strk
     Type:  defect                 |     Status:  new
 Priority:  blocker                |  Milestone:  PostGIS 3.0.0
Component:  build/upgrade/install  |    Version:  trunk
 Keywords:                         |
-----------------------------------+---------------------------
 Okay I know pramsey will want to scream a "Well if it hurts don't do that"
 my way.

 Here it is the dirty pg_upgrading from PostgreSQL 9.4 2.4.6 to PostgreSQL
 11 3.0.0dev


 1.  Have PostgreSQL 9.4 instance installed with PostGIS 2.4.6 and a
 database test24 with postgis installed in it.

 2. On my PostgreSQL 11 instance, have postgis 3.0 installed and make a
 copy of the postgis-3.dll, rtpostgis-3.dll to postgis-2.4.dll,
 rtpostgis-2.4.dll

 3. Run pg_upgrade from 9.4 to PG 11

 4) Start up my PG 11 and run


 {{{
 SELECT postgis_full_version();
 }}}

 output is this - so far so good -yeh our version checker is smart enough
 to know I tricked it with a 3.0 lib, but I'm running 2.4.6 scripts (and
 2.4.6 scripts from a 9.4 install yeh and yeh)

 {{{
 POSTGIS="3.0.0dev r16983" PGSQL="94" (procs need upgrade for use with
 "110") GEOS="3.7.0-CAPI-1.11.0 673b9939" PROJ="Rel. 5.2.0, September 15th,
 2018" GDAL="GDAL 2.3.1, released 2018/06/22" LIBXML="2.7.8"
 LIBPROTOBUF="1.2.1" (core procs from "2.4.6 r17057" need upgrade) RASTER
 (raster procs from "2.4.6 r17057" need upgrade)
 }}}


 5) Run


 {{{
 ALTER EXTENSION postgis UPDATE TO "3.0.0dev";
 }}}

 FAILS

 with

 {{{

 ERROR:  operator does not exist: gidx public.&& geography
 LINE 1: SELECT $2 OPERATOR(public.&&) $1;
 }}}

 Like a monkey expecting something different to happen by pressing the
 button again, I try again


 {{{
 WARNING:  unpackaging raster
 WARNING:  PostGIS Raster functionality has been unpackaged
 HINT:  type `SELECT postgis_extensions_upgrade(); to finish the upgrade.
 After upgrading, if you want to drop raster, run: DROP EXTENSION
 postgis_raster;

 ******* Error *******

 ERROR:  operator does not exist: gidx public.&& geography
 LINE 1: SELECT $2 OPERATOR(public.&&) $1;
                   ^
 HINT:  No operator matches the given name and argument types. You might
 need to add explicit type casts.
 QUERY:  SELECT $2 OPERATOR(public.&&) $1;
 SQL state: 42883
 }}}


 I don't think I got that message the first time.

 I try a 3rd time.


 {{{

 ALTER EXTENSION postgis UPDATE TO "3.0.0dev";

 }}}

 Hey how come no unpacking raster message this time:


 {{{
 ERROR:  operator does not exist: gidx public.&& geography
 LINE 1: SELECT $2 OPERATOR(public.&&) $1;
                   ^
 HINT:  No operator matches the given name and argument types. You might
 need to add explicit type casts.
 QUERY:  SELECT $2 OPERATOR(public.&&) $1;
 SQL state: 42883
 }}}

-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/4257>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.