EXTENSION in-place upgrade from 2.0 from 2.1 leaves topology procs not upgraded

[zenitram]

I'm testing several ways to upgrade from POSTGIS="2.0.4 r11936" to POSTGIS="2.1.0 r11822". The old databases have PostGIS functions loaded manually without the extension mechanism.

I did the following: -CREATE EXTENSION postgis FROM unpackaged; -CREATE EXTENSION postgis_topology FROM unpackaged; -upgrade system libraries and PostGIS extensions to 2.1 -ALTER EXTENSION postgis UPDATE TO "2.1.0"; -ALTER EXTENSION postgis_topology UPDATE TO "2.1.0";

Doing this returns no error, but postgis_full_version() indicates:

POSTGIS="2.1.0 r11822" GEOS="3.3.8-CAPI-1.7.8" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.9.0, released 2011/12/29" LIBXML="2.7.8" LIBJSON="UNKNOWN" TOPOLOGY (topology procs from "2.0.3 r11128" need upgrade) RASTER

(1 row)

[strk]

I don't know if it's related, but also running this:

CREATE EXTENSION postgis_topology version '2.0.3'

Doesn't change postgis_full_version() output to advertise topology !

 POSTGIS="2.0.3 r11132" GEOS="3.4.3dev-CAPI-1.8.3 r3958" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.9.2, released 2012/10/08" LIBXML="2.7.8" LIBJSON="UNKNOWN" RASTER

Still, there _is_ a topology.topology table created in there. What's the deal ? Robe ?

[strk]

In my case re-starting the backend fixed the issue. Zenitram: does a new session make any difference for you ?

[strk]

I found my problem and filed a new bug for it: #2502

[zenitram]

No, restarting postgresql and psql still leaves topology needing upgrade for me

[robe]

hmm I upgraded mine to 2.1.0 and shows up fine. Which packaging are you using? One you built yourself or from some distribution?

[zenitram]

The new package comes from ​http://apt.postgresql.org/pub/repos/apt/pool/main/p/postgis/.

The old package was installed from ​https://launchpad.net/~cartodb/+archive/gis, I think it was built from Ubuntu standard package

[strk]

Zenitram, which package is it, exactly ? Would you know where to file a ticket for packaging bugs ?

Also, take a look at advertised version in these files (path may be different in your case): grep 'AS version' /usr/share/postgresql/9.1/extension/postgis_topology*—2.1.1dev.sql

[strk]

I could actually reproduce the problem, with code from tag 2.0.3 and code from tag 2.1.0. Steps to reproduce:

1. create db "test", load postgis/raster/topology using 2.0.3 scripts
2. install postgis 2.1.0
3. CREATE EXTENSION postgis VERSION '2.0.3' FROM unpackaged;
4. CREATE EXTENSION postgis_topology VERSION '2.0.3' FROM unpackaged;
5. ALTER EXTENSION postgis UPDATE TO '2.1.0';
6. ALTER EXTENSION postgis_topology UPDATE TO '2.1.0';

At the end of the steps, postgis_full_version reports:

POSTGIS="2.1.0 r11822" (topology procs from "2.0.3 r11132" need upgrade)

I'll note that r11822 corresponds to tag 2.1.0 and r11132 is tag 2.0.3

[strk]

Ok I can see that the the problem is indeed bug #2502, in that after the upgrade of topology extension from 2.0.3 to 2.1.0 we end up having two different versions of "postgis_topology_scripts_installed". One in the public schema, and the other in the "topology" schema.

Up to right after CREATE EXTENSION FROM unpackaged there's a single one, and it is in public. Running "ALTER EXTENSION postgis_topology UPDATE TO '2.1.0'" create the new function under topology and doesn't drop the one in public.

[strk]

Robe what changed between 2.0 and 2.1 in postgis_topology extension ? It sounds like the regression was in the extension scripts, and that things could work before that ?

[strk]

In any case, upgrading the extension to 2.1.1 will fix this, so assuming fixed (by #2502).

[robe]

I tried this by upgrading from 2.0.4 to 2.1.1dev using extensions and I don't see anything left behind anymore.