[postgis-users] Problem with the 1.5 upgrade script
woodbri at swoodbridge.com
Thu Jan 19 20:52:35 PST 2012
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:
-- remove postgis from "data" schema
-- point path to "public" and upgrade that again
set search_path to public;
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.
More information about the postgis-users