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

[olivier.lobry at umontpellier.fr]

Hi

Thank you for the suggestions.

I’ve finally found that the problem lies in the creation of the temporary _postgis_upgrade_info table :
    241 CREATE TEMPORARY TABLE _postgis_upgrade_info AS WITH versions AS (
    242   SELECT '2.2'::text as upgraded,
    243   postgis_scripts_installed() as installed
    244 ) SELECT
    245   upgraded as scripts_upgraded,
    246   installed as scripts_installed,
    247   substring(upgraded from '([0-9]*)\.')::int * 100 +
    248   substring(upgraded from '[0-9]*\.([0-9]*)\.')::int
    249     as version_to_num,
    250   substring(installed from '([0-9]*)\.')::int * 100 +
    251   substring(installed from '[0-9]*\.([0-9]*)\.')::int
    252     as version_from_num,
    253   position('dev' in  installed)::bool
    254     as version_from_isdev
    255   FROM versions
    256 ;

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) ?

Regards

Olivier

> Le 26 juin 2019 à 13:00, Sandro Santilli <strk at kbt.io> a écrit :
> 
> On Wed, Jun 26, 2019 at 11:20:59AM +0200, olivier.lobry at umontpellier.fr wrote:
>> Hi,
>> 
>> On a postgresql 9.3 / postgis 2.1.8 / debian 8.11, I cannot upgrade postgis to 2.2.2 (or 2.3.3) :
> 
>> 
>> # ALTER EXTENSION postgis UPDATE TO '2.2.2';
>> WARNING:  nonstandard use of escape in a string literal
>> HINT:  Use the escape string syntax for escapes, e.g., E'\r\n'.
> [...]
>> ERROR:  invalid input syntax for integer: «  »
>> 
>> Any idea of what happens ?
> 
> Note that latest PostGIS version in the 2.2 branch is
> 2.2.8 (released on 2018/11/21) and latest in the 2.3 branch is
> 2.3.8 (released on 2019/03/11).
> Also, latest stable is 2.5.2 (released on 2019/03/11)
> 
> Before moving on, I'd try *those* upgrades.
> 
>> How can I get more debug information ?
>> Is it possible to execute the command step-by-step to see where the underlying script fails ?
> 
> Not with the "EXTENSION" mechanism, but you could try the
> script-way, using psql -f which would give you a line number
> of the failing statement.
> 
> Please show the output of `SELECT postgis_full_version()" issued
> on your 2.1.8 install, and beware that a newer release in the 2.1
> branch of PostGIS is also available: PostGIS 2.1.9 (2017/09/19)
> 
> --strk;
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users