Error soft-upgrading from 2.1 to 2.2

[strk]

Cannot upgrade (via scripts) from 2.0.7dev to 2.1.6dev:

DB=pgis_upgrade_test                                                            
PSQL="psql --set ON_ERROR_STOP=1"                                               
dropdb $DB                                                                      
createdb $DB                                                                    
${PSQL} -f postgis-2.0-9.1/postgis/postgis.sql $DB                              
${PSQL} -f postgis-2.0-9.1/raster/rt_pg/rtpostgis.sql $DB                       
${PSQL} -f postgis-2.1-9.1/postgis/postgis_upgrade.sql $DB                      
${PSQL} -f postgis-2.1-9.1/raster/rt_pg/rtpostgis_upgrade.sql $DB

[strk]

false alarm, it looks like I can upgrade now, might have had some non-updated object around.

[strk]

reopening, now I get:

psql:postgis-2.2-9.1/raster/rt_pg/rtpostgis_upgrade.sql:1002: NOTICE:  type "summarystats" is not yet defined
DETAIL:  Creating a shell type definition.
CREATE FUNCTION
psql:postgis-2.2-9.1/raster/rt_pg/rtpostgis_upgrade.sql:1010: ERROR:  SQL function cannot return shell type summarystats

[strk]

See also #3081, as r13348 might have introduced this.

[strk]

It looks like the type "summarystats" existed in 2.0.0 but was removed in 2.1.0. Definitions in 2.0 and 2.2 are identical. Do you confirm, Bborie and Regina ?

[strk]

I can confirm this myself. Now we have some problem in that:

• upgrading from 2.0 to 2.1 must DROP the type
• upgrading from 2.0 to 2.2 must NOT DROP the type

Automating this will require the definition of a label to use (like the Availability one) for representing the need to pre-drop a type before re-creating it.

That is, we currently say that he availability for "summarystats" is 2.2, but instead it was also available in 2.0. Maybe a "Changed" (although not strictly true) could do.

[strk]

With r13349 I've made postgis_proc_upgrade.pl aware of "Missing in" labels, and added it to "summarystats" type (Availability: 2.0.0; Missing in: 2.1.0).

After also fixing availability info for "agg_count" ( r13350 ) both upgrades from 2.0 and 2.1 work for me (tested via scripts).

[lrssvt]

I am experiencing the same error when upgrading to 2.2.0 via scripts. After running the rtpostgis_upgrade.sql script I am getting:

psql:/home/sam/pacchetti_gis/postgis-2.2.0/raster/rt_pg/rtpostgis_upgrade.sql:1019:
NOTICE:  type "summarystats" is not yet defined
DETAIL:  Creating a shell type definition.
CREATE FUNCTION
psql:/home/sam/pacchetti_gis/postgis-2.2.0/raster/rt_pg/rtpostgis_upgrade.sql:1027:
ERROR:  SQL function cannot return shell type summarystats

while all fine with postgis and topology scripts.

[strk]

Salvatore: upgrading from which version ? Can you show the output of "SELECT postgis_full_version()" in the source database ?

[lrssvt]

upgrading from 2.2.0dev r12038

POSTGIS="2.2.0 r14208" GEOS="3.5.0-CAPI-1.9.0 r4084" SFCGAL="1.2.0" PROJ="Rel. 4.7.1, 23 September 2009" GDAL="GDAL 1.11.0, released 2014/04/16" LIBXML="2.8.0" TOPOLOGY RASTER (raster procs from "2.2.0dev r12038" need upgrade)

[strk]

Did you get to 2.2.0dev in an upgrade from 2.1.0 ? I suspect you had a broken 2.2.0dev, basically, due to this bug.

The upgrade script won't add it from 2.2.0 to 2.2.0, but it should add it from 2.1.x to 2.2.0. Try sourcing rtpostgis_upgrade.sql from 2.1.0 first and then get back to this 2.2.0 one.

Or you could manually edit the rtpostgis_upgrade.sql from 2.2.0 to tweak the conditional summarystats addition:

 -- Type summarystats -- LastUpdated: 200
 DO LANGUAGE 'plpgsql'
 $postgis_proc_upgrade$
 BEGIN
   IF 200 > version_from_num
 OR version_from_num IN ( 201 )     FROM _postgis_upgrade_info
   THEN

[lrssvt]

2.2.0dev has always worked very well, no problem. A while ago I did upgrade constantly from trunk, now a little less, so yes 2.2.0dev is an upgrading from 2.1.0dev.

Tweaking the conditional in the script does the job, thanks (I had to tweak type agg_count as well)

[strk]

Another way could have been to replace your postgis_scripts_installed() to pretend you were running 2.1 instead of 2.2. Anyway, I guess it's too late for testing that now …

Closing again, assuming the "cheating" approach would work (if you can test and it doesn't work, reopen again).