[postgis-devel] Upgrade PG 9.3 to 9.5 with PostGIS issue

[Sandro Santilli]

On Tue, Apr 05, 2016 at 09:46:50AM +0000, Blumentrath, Stefan wrote:
> Many thanks.
> 
> The query returns a list of 153, quite basic functions, e.g.:
> "buffer(geometry, double precision)"
> "buildarea(geometry)"
> "centroid(geometry)"
> "contains(geometry, geometry)"
> "convexhull(geometry)"
> "crosses(geometry, geometry)"
> "difference(geometry, geometry)"
> "dimension(geometry)"
> "disjoint(geometry, geometry)"
> "distance(geometry, geometry)"
> "distance_sphere(geometry, geometry)"
> "distance_spheroid(geometry, geometry, spheroid)"
> "dump(geometry)"
> "dumprings(geometry)"

All those signatures are missing an "st_" or "postgis_" prefixes,
meaning they probably come from pre-2.0 times, OR have been installed
via "legacy.sql".

> Does that mean the upgrade of the extension failed locally?

The upgrade procedure doesn't attempt to upgrade the legacy functions,
so their presence is not necessarely an indication of an upgrade failure.

If you can tell how those functions got in there, it might help
determining if it was an upgrade bug to keep them in or not.

> Do I have to run some sql scripts from /usr/share/postgresql/9.5/extension/ manually?

I dubt there's anything extension-specific about those functions.

This is a list of functions associated to the 'postgis' extension:

 SELECT pg_describe_object(d.classid, d.objid, 0)
   FROM pg_depend d, pg_extension e
  WHERE d.refobjid = e.oid
    AND d.refclassid = 'pg_extension'::regclass
    AND d.classid = 'pg_proc'::regclass
    AND deptype = 'e' AND e.extname = 'postgis';

A function associated to an extension cannot be removed with
DROP FUNCTION

You could tweak the function you used to _list_ those functions
to add a DROP FUNCTION and then run the resulting sql. Or, if
you installed those functions via loading "legacy.sql", you could
try loading "uninstall_legacy.sql" but it should be coming from
the same version the "legacy.sql" came from.

--strk;