[postgis-users] Problem with the 1.5 upgrade script

[Stephen Woodbridge]

Hi All,

I recently upgraded to

"POSTGIS="1.5.3" GEOS="3.2.0-CAPI-1.6.0" PROJ="Rel. 4.6.1, 21 August 
2008" LIBXML="2.6.32" USE_STATS"

And I just notice to my horror that the upgrade script does not pay 
attention to schemas. My standard database setup is along these lines:

createdb -T template_postgis_15 mydb
psql mydb -c "create schema data"
psql mydb -c "alter database mydb set search_path to data, public"

Then all my data and stored procedures land in the "data" schema and all 
the postgis stuff is in the "public" schema.

BUT when a run the postgis_upgrade_15_minor.sql with that search_path 
set dumps all the procedures into "data" and makes a huge mess! and does 
not remove the functions in public.

To make matters worse, I upgraded about 50-100 databases before I 
realized this had happened :(

So is the fix as easy as running:

psql mydb

-- remove postgis from "data" schema
\i uninstall_postgis.sql

-- point path to "public" and upgrade that again
set search_path to public;
\i postgis_upgrade_15_minor.sql

\q

on every database that I have again?

OK, I do not think the above will work after looking at the script 
because it does:

DROP TYPE .... CASCADE;

Which I assume will cascade to all my tables that have geometry stored 
in them which would be bad!

So I edited the uninstall_postgis.sql and commented out all the DROP 
TYPE and DROP OPERATOR lines so basically just the DROP FUNCTION lines 
are left. There were also about 52 functions that did not get dropped 
and I had to add them to the script also. So now running the modified 
uninstall_postgis.sql in the script above seems to work. I will try to 
automate this to run on all the databases.

Regardless, I think the various upgrade scripts should be schema aware 
in the future. This could be as simple as adding:

  SET search_path to public;

in the transaction so the correct functions get removed and updated.


Thanks,
   -Steve