Upgrade from 2.0.5: ERROR: type "rastbandarg" already exists

[strk]

Upgrading from 2.0.5 to 2.2.0 using extension fails with:

postgis_reg=# select postgis_version();
            postgis_version            
---------------------------------------
 2.0 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
(1 row)

postgis_reg=# ALTER EXTENSION postgis UPDATE TO '2.2.0dev';
ERROR:  type "rastbandarg" already exists
CONTEXT:  SQL statement " CREATE TYPE rastbandarg AS (
        rast raster,
        nband integer
);
 "
PL/pgSQL function "inline_code_block" line 4 at EXECUTE statement

[strk]

Also happens with script (non-extension) based upgrades:

DB=pgis_upgrade_test                                                            
createdb $DB                                                                    
psql -f postgis-2.0-9.1/postgis/postgis.sql $DB                                 
psql -f postgis-2.0-9.1/raster/rt_pg/postgis.sql $DB                            
psql -f postgis-2.2-9.1/postgis/postgis_upgrade.sql $DB                         
psql -f postgis-2.2-9.1/raster/rt_pg/rtpostgis_upgrade.sql $DB   

[strk]

Sorry, updated script:

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.2-9.1/postgis/postgis_upgrade.sql $DB                      
${PSQL} -f postgis-2.2-9.1/raster/rt_pg/rtpostgis_upgrade.sql $DB

[strk]

The problem is that rtpostgis_upgrade.sql contains two different code blocks taking care of creating types. One is the postgis_proc_upgrade.pl generated block:

-- Type rastbandarg -- LastUpdated: 201                                         
DO LANGUAGE 'plpgsql'                                                           
$postgis_proc_upgrade$                                                          
BEGIN                                                                           
  IF 201 > version_from_num FROM _postgis_upgrade_info THEN                     
    EXECUTE $postgis_proc_upgrade_parsed_def$ CREATE TYPE rastbandarg AS (      
  rast raster,                                                                  
  nband integer                                                                 
);                                                                              
 $postgis_proc_upgrade_parsed_def$;                                             
  END IF;                                                                       
END                                                                             
$postgis_proc_upgrade$;           

The other one is what I belive being a manually crafted block:

    -- create rastbandarg type if it does not exist                             
  IF NOT EXISTS(SELECT typname                                                  
    FROM pg_type                                                                
          WHERE typname = 'rastbandarg') THEN                                   
      CREATE TYPE rastbandarg AS (                                              
        rast raster,                                                            
        nband integer                                                           
      );                                                                        
    END IF;      

[strk]

Indeed the manually crafted one is in rtpostgis_upgrade_cleanup.sql.in. I think we should drop all those CREATE TYPE IF NOT EXIST snippets from that file and rely on the proc_upgrade.pl generated handling of it instead.

[strk]

Fixed with r13348