[postgis-users] fail to upgrade from 2.1.8 to 2.2.2 or 2.3.3

[olivier.lobry at umontpellier.fr]

> Le 27 juin 2019 à 17:59, Sandro Santilli <strk at kbt.io> a écrit :
> 
> On Thu, Jun 27, 2019 at 12:46:03PM +0200, olivier.lobry at umontpellier.fr wrote:
> 
>> at line 248, substring(upgraded from '[0-9]*\.([0-9]*)\.’) cannot be cast to int since it leads ‘’ because of the « SELECT '2.2'::text as upgraded » at line 242.
>> 
>> replacing SELECT '2.2'::text as upgraded with SELECT ‘2.2.2'::text as upgraded solves the issue
>> 
>> Looks like there is the same problem with any migration scripts from 2.1.xx 
>> 
>> Maybe a problem of compilation/debian distribution (since I installed postgis using debian packages) ?
> 
> I guess we could just pick the first two numbers and drop the final
> '\.' portion of the regexp... What surprises me is that our CI bots
> testing upgrades did not catch this issue.

yes and what surprises me is that the statement is wrong whatever the database it is ran against (since the ‘2.2’ is hard coded)

> Maybe it is only
> coming from your old 2.1.xx version rather than the versions
> tested by CI (2.1.9) ?

yes, this is likely. I upgraded my debian from wheezy to jessie just before doing the postgis upgrade. The script surely comes from an old wheezy deb package:

# ls -l /var/cache/apt/archives/postgresql-9.3-postgis-2.1-scripts_2.1.8+dfsg-5~97.git43a09cc.pgdg80+1_all.deb 
-rw-r--r-- 1 root root 365986 Sep 18  2015 /var/cache/apt/archives/postgresql-9.3-postgis-2.1-scripts_2.1.8+dfsg-5~97.git

> 
> Also, you didn't show the output of:
> 
>  SELECT Postgis_full_version()

here it is :
                                                                                                                                                    postgis_full_version                                                                                                                                                     
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 POSTGIS="2.1.8 r13780" GEOS="3.3.3-CAPI-1.7.4" PROJ="Rel. 4.7.1, 23 September 2009" GDAL="GDAL 1.9.0, released 2011/12/29" LIBXML="2.8.0" LIBJSON="UNKNOWN" (core procs from "2.1.3 r12547" need upgrade) TOPOLOGY (topology procs from "2.0.3 r11128" need upgrade) RASTER (raster procs from "2.1.3 r12547" need upgrade)

note that I fixed the « need upgrade… » but still had the issue

Olivier