PostGIS upgrade is broken - operator does not exist: gidx public.&& geography

[komzpa]

gis=# select postgis_full_version();
                                                                                                                      postgis_full_version                                                                                                                      
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 POSTGIS="2.3.0rc1dev" GEOS="3.5.0-CAPI-1.9.0 r4084" PROJ="Rel. 4.9.2, 08 September 2015" GDAL="GDAL 1.11.3, released 2015/09/16" LIBXML="2.9.3" LIBJSON="0.11.99" (core procs from "2.3.0dev" need upgrade) RASTER (raster procs from "2.3.0dev" need upgrade)
(1 row)

gis=# alter extension postgis update ;
ERROR:  operator does not exist: gidx public.&& geography
LINE 2:   SELECT $2 OPERATOR(public.&&) $1;
                    ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
QUERY:  
  SELECT $2 OPERATOR(public.&&) $1;

[robe]

komzpa,

I ran into this myself. I'm guessing you were trying to upgrade from an earlier 2.3.0. So this would be a non-issue for production upgrades.

The reason you are getting this is because of the BRIN introduction, and our upgrade logic can't handle index binding changes within a micro update. So since you are going from a 2.3.0 before brin was introduced to 2.3.0 after brin, you are having this issue.

As I recall when testing upgrading from a 2.2 to 2.3.0 this was a non-issue.

To fix, as I recall, I had to take out the

IF 203 > … wrapper around the BRIN stuff so that the brin operators and families could be created.

-- Operator family brin_geography_inclusion_ops brin -- LastUpdated: 203
DO LANGUAGE 'plpgsql'
$postgis_proc_upgrade$
BEGIN
  IF 203 > version_from_num FROM _postgis_upgrade_info THEN
    EXECUTE $postgis_proc_upgrade_parsed_def$ CREATE OPERATOR FAMILY brin_geography_inclusion_ops USING brin;

-- Availability: 2.3.0
CREATE OR REPLACE FUNCTION geog_brin_inclusion_add_value(internal, internal, internal, internal) RETURNS boolean
        AS '$libdir/postgis-2.3','geog_brin_inclusion_add_value'
        LANGUAGE 'c';

-- Availability: 2.3.0
CREATE OPERATOR CLASS brin_geography_inclusion_ops
  DEFAULT FOR TYPE geography
  USING brin
  FAMILY brin_geography_inclusion_ops AS
    OPERATOR      3        &&(geography, geography),
    FUNCTION      1        brin_inclusion_opcinfo(internal) ,
    FUNCTION      2        geog_brin_inclusion_add_value(internal, internal, internal, internal) ,
    FUNCTION      3        brin_inclusion_consistent(internal, internal, internal) ,
    FUNCTION      4        brin_inclusion_union(internal, internal, internal) ,
  STORAGE gidx;

ALTER OPERATOR FAMILY brin_geography_inclusion_ops USING brin ADD
    OPERATOR      3         &&(gidx, geography),

    OPERATOR      3         &&(geography, gidx),

    OPERATOR      3         &&(gidx, gidx);
 $postgis_proc_upgrade_parsed_def$;
  END IF;
END
$postgis_proc_upgrade$;

I think strk has a ticket in place already for this kind of thing (to make dev upgrades possible even in these cases) though I can't find it at moment

[robe]

komzpa,

I just upgraded an old 2.3.0dev to 2.3.0rc1.

To do so I changed the share/extension/postgis—2.3.0dev—2.3.0rc1.sql

By just replacing all references to 203 with 204. That way it will force the brin create logic to happen. Liek I said this is only an issue for a 2.3.0dev install before the BRIN index logic was committed and our upgrade plumming considers a 2.3.0dev a micro so not allowed to have new operators etc.

[komzpa]

This is seen upgrading from 9.3 with PostGIS 2.3 where no BRIN was installed:

[19:41] <tomorrow__> I'm seeing this issue when upgrading postgis https://trac.osgeo.org/postgis/ticket/3633
[19:41] <sigq> Title: #3633 (PostGIS upgrade is broken - operator does not exist: gidx public.&& geography) – PostGIS (at trac.osgeo.org)
[20:59] <Komzzpa> tomorrow__: you updated from never released 2.3.0pre?
[21:05] <tomorrow__> Komzzpa: I was trying to update after a pg_upgrade from 9.3 to 11
[21:05] <tomorrow__> postgis 2.3 -> to postgis 2.5

[Algunenano]

Duplicated in #4257, so there is more information about how to reproduce it there

[pramsey]

Is this still a thing?

[robe]

Not sure. Have to upgrade from a 9.3 2.3 to a 2.5 or 3.0. At any rate, if it needs fixing, it should be done at least for 2.5 and above. so pushing this back one milestone to get out of 3.0 way

[pramsey]

Sorry, this is only for Pg 9.3? EOL? Close it, yes?