[postgis-users] Proper way to upgrade after using CREATE EXTENSION postgis?

[Sandro Santilli]

On Mon, Jun 25, 2012 at 11:02:44AM -0700, Charlie Sharpsteen wrote:
> Hello all,
> 
> I installed PostGIS 2.0 and added it to a database using:
> 
>     CREATE EXTENSION postgis;
> 
> 
> Today, I built and installed 2.0.1 and tried to upgrade the extension using:
> 
>     ALTER EXTENSION postgis UPDATE;
> 
> 
> However, this failed with:
> 
>     ERROR:  cannot drop cast from geometry to geometry because extension 
> postgis requires it
>     HINT:  You can drop extension postgis instead.
> 
> 
> So, I tried the old school way of running the upgrade scripts:
> 
>     psql -f /usr/local/share/postgis/postgis_upgrade_20_minor.sql 
> hum_bay_gis
> 
> 
> But this also fails:
> 
> 
> BEGIN
> CREATE FUNCTION
>        postgis_major_version_check        
> ------------------------------------------
>  Scripts versions checked for upgrade: ok
> (1 row)
> 
> DROP FUNCTION
> psql:/usr/local/share/postgis/postgis_upgrade_20_minor.sql:46: ERROR: 
>  cannot drop function addgeometrycolumn(character varying,character 
> varying,character varying,character varying,integer,character 
> varying,integer,boolean) because extension postgis requires it
> HINT:  You can drop extension postgis instead.
> psql:/usr/local/share/postgis/postgis_upgrade_20_minor.sql:47: ERROR: 
>  current transaction is aborted, commands ignored until end of transaction 
> block
> ...(thousands of similar errors)...
> ROLLBACK
> 
> 
> So, it appears there is no way to upgrade PostGIS without running `DROP 
> EXTENSION postgis` which would cascade to all spatially-enabled tables.
> 
> Am I missing a step or is there a circular dependency here that is breaking 
> the upgrade process?

I had a similar experience with EXTENSION, so much that I decided not to
use it again. It's a bad step back from safe and well-tested old school way.

I hope you've a way to break out of the EXTENSION dependency jail somehow.
Please report back when you figure.

--strk;

 Sent from our free software
 http://www.gnu.org/philosophy/free-sw.html