Upgrade scripts PostGIS 1.4/1.5 need some work

[robe]

I think the 1.4 has same issues. I think maybe we should move the DROP IF EXISTS functions out of COMMIT; block or handle the dependencies correctly.

I had to do that to get the upgrade to work. For example I get this when upgrading my PostGIS 1.5

ERROR: cannot drop function geom_accum(geometry[],geometry) because other objects depend on it DETAIL: function geomunion_old(geometry) depends on function geom_accum(geometry[],geometry) HINT: Use DROP ... CASCADE to drop the dependent objects too.

********** Error **********

ERROR: cannot drop function geom_accum(geometry[],geometry) because other objects depend on it SQL state: 2BP01 Detail: function geomunion_old(geometry) depends on function geom_accum(geometry[],geometry) Hint: Use DROP ... CASCADE to drop the dependent objects too.

[pramsey]

What is the proper handling? If there's a user-defined dependent object (note, the dependencies are not *system*-defined, I've tested all these upgrade scripts against standard cases) what possible behavior can we define to handle the problem automatically? The only thing left is dropping the transaction block, which will allow a partial upgrade to succeed and mostly work most of the time. Works for me, but then I'm a cowboy.

[robe]

I wouldn't say drop the transaction block -- just move the commit up before the drop.

I don't have any user created ones in the ones I've been testing --but I do have the geomunion_old you are trying to drop and that depends on the other functions you had.

How I got so lucky I don't know. But none of the dbs that started out as 1.4.0 have been able to upgrade without moving drop out of the commit. Perhaps I have OUT RECKLIFIED you as my installs are nto as prisitine as yours evidentally :).

[pramsey]

What is your testing process then? I create a 1.3 or 1.4 database, shp2pgsql a table into it, then run it up through the succeeding versions, either one at a time or directly into the latest.

[robe]

Which 1.3 or 1.4 are you using (I think 1.3 to 1.4.1 upgrades may be fine - but 1.4.0 to 1.4.1 is not). Have no testing just reload my old db onto my test box and try to upgrade. My dbs have gone thru several minor major upgrades.

It could be one started out with a beta 1.4 (but I do believe your geom_union old accidentally made it into 1.4.0 or that could have been our mistake that we accidentally packaged the wrong .sql file.) Will have to double-check that.

[pramsey]

geomunion_old doesn't exist in the postgis 1.4 SQL install script. (Curiously, it exists in the uninstall script though.) Where are you getting this database from that includes geomunion_old?

[pramsey]

I'm testing of the svn heads of the various branches, but I just pulled the 1.4.0 tarball and confirmed that a minor upgrade from 1.4.0 to 1.4.1 works fine.

[robe]

Yah I confirmed its not in our windows 1.4.0 release either. Must have been databases I upgraded from 1.4.0b or some svn.

I only see these in 1.4.0 -- so since only reckless people like me should have those, in theory we can remove them from your uninstall (or at least move those ones out of the transaction)

-- TO BE REMOVED BEFORE RELEASE
CREATE AGGREGATE accum_old (
	sfunc = ST_geom_accum,
	basetype = geometry,
	stype = geometry[]
	);

-- TO BE REMOVED BEFORE RELEASE
CREATE AGGREGATE ST_accum_old (
	sfunc = ST_geom_accum,
	basetype = geometry,
	stype = geometry[]
	);