[postgis-devel] postgis_proc_upgrade.pl dropping aggregates
Mark Cave-Ayland
mark.cave-ayland at ilande.co.uk
Wed Aug 22 06:11:56 PDT 2007
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
More information about the postgis-devel
mailing list