[postgis-devel] postgis_proc_upgrade.pl dropping aggregates

[Mark Cave-Ayland]

On Wed, 2007-08-22 at 08:55 -0400, Obe, Regina wrote:
> This is actually a continuation of the thread on Postgis users about
> Collect vs. ST_Collect GeomUnion vs ST_GeomUnion, but thought it would
> be better dealt with in this group.
>  
> Looking at the 
> http://svn.refractions.net/postgis/trunk/utils/postgis_proc_upgrade.pl
>  
> Commented out section on aggregate functions.  I see we are doing a 
> 
> DROP AGGREGATE $aggname($basetype);\n
> 
> I was going to change to do
> 
> DROP AGGREGATE IF EXISTS $aggname($basetype);\n
> 
> But I'm not quite sure when IF EXISTS was introduced into Postgresql.
> Does anyone have an idea when that was introduced and if we did change
> the above to something like that if it would pose problems elsewhere.
> 
> I guess we would still have the problem if someone is using the
> aggregate functions in a view or something like that it would fail a
> drop.  Haven't quite figured out a workaround for that so its still not
> as nice as a CREATE OR REPLACE would be.
> 
> Thanks,
> Regina


Actually thinking about this more: I don't think the scripts upgrade can
work across a major version upgrade because the upgrade script cannot
know the old version of PostGIS, and hence which functions need to be
removed from the existing database :(

In fact, towards the bottom I see this:

	IF old_maj != new_maj THEN
		RAISE EXCEPTION ''Scripts upgrade from version % to version % requires a dump/reload. See postgis manual for instructions'', old_scripts, new_scripts;
	ELSE
		RETURN ''Scripts versions checked for upgrade: ok'';
	END IF;

Did this exception not show up during your upgrade at all?

<thinks harder>

Perhaps a better idea would be to work on
http://svn.refractions.net/postgis/trunk/utils/create_undef.pl again so
that it parses the lwpostgis.sql file at build time to create a .sql
file that removes all of the PostGIS functions/types/operators for the
current version.

We could then build this into the database as a function that returns
the text of the resulting undef SQL file as text, e.g. so SELECT
postgis_uninstall_script() returns a single text field containing the
contents of this file.

Then if we need to upgrade at all, the upgrade client can just do SELECT
postgis_uninstall_script(), and then execute the resulting text on the
server to uninstall the old version; then aferwards it can execute the
new scripts to load in the new functions.

</thinks harder>


Thoughts?

Mark.

-- 
ILande - Open Source Consultancy
http://www.ilande.co.uk