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

Blumentrath, Stefan Stefan.Blumentrath at nina.no
Wed Apr 6 03:25:29 PDT 2016


Hi again,

And thank you so much for all your assistance! The library issue is now solved by means of removing the legacy functions (where I could not remember when they were added).

I got following error message:
pg_restore: creating MATERIALIZED VIEW "nofa_tmp.stasjons_innsjoer"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2384; 1259 30178832 MATERIALIZED VIEW stasjons_innsjoer stefan
pg_restore: [archiver (db)] could not execute query: ERROR:  operator does not exist: public.geometry && public.geometry
LINE 1: SELECT $1 && $2 AND _ST_Intersects($1,$2)
                  ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
QUERY:  SELECT $1 && $2 AND _ST_Intersects($1,$2)
CONTEXT:  SQL function "st_intersects" during inlining
    Command was: 
-- For binary upgrade, must preserve pg_type oid
SELECT pg_catalog.binary_upgrade_set_next_pg_type_oid('30178834'::pg_catal...

This occurred also after running:
postgis-2.2/postgis_proc_set_search_path.sql in my old database...

After removing the (luckily few) Materialized Views in my database pg_upgrade proceeds (and is now copying the data which I guess will take some time...

Cheers
Stefan


-----Original Message-----
From: Sandro Santilli [mailto:sandro.santilli at gmail.com] On Behalf Of Sandro Santilli
Sent: 5. april 2016 12:59
To: Blumentrath, Stefan <Stefan.Blumentrath at nina.no>
Cc: PostGIS Development Discussion <postgis-devel at lists.osgeo.org>
Subject: Re: [postgis-devel] Upgrade PG 9.3 to 9.5 with PostGIS issue

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;



More information about the postgis-devel mailing list