[postgis-devel] postgis_upgrade.sql

[Mark Cave-Ayland]

Kevin Neufeld wrote:

> I kinda agree.  Wouldn't we have to have an upgrade script for every 
> permutation.

Well, not every permutation. We'd only agree to support certain 
upgrades, say maybe 2 minor versions due to the limited testing 
resource. So for 1.5 we'd just provide:

upgrade_1.3_to_1.5.sql
upgrade_1.4_to_1.5.sql

If in the future people wish to upgrade from say 1.3 to 1.6 then due to 
the new versioning in 1.4 then they can go via an intermediate release 
to get to the final version. A bit messy, but then at least someone with 
expertise can keep the database consistent.

> What if instead of a one large plpgsql script, we were to wrap all the 
> function/casts/type/etc declarations in their own plpgsql script that 
> does a simple test and upgrades the function/type if needed.
> ie.
> 
> CREATE OR REPLACE FUNCTION upgrade_function() RETURNS text AS
> $$
> DECLARE
>   postgisVersion text;
> BEGIN
>   postgisVersion := postgis_version();
>   IF (postgisVersion < '1.3') THEN
>     EXECUTE 'DROP FUNCTION deprecated_postgis_function';
>   END IF;
>   EXECUTE 'CREATE OR REPLACE ST_Box(box3d) ...';
>   RETURN 'done';
> END
> $$ LANGUAGE plpgsql;
> SELECT upgrade_function();
> DROP FUNCTION upgrade_function();
> 
> I don't know, this seems quite unwieldy too.
> -- Kevin

No, I don't like this at all. The reason being that when fixing broken 
upgrades manually, I often end up cutting and pasting various chunks out 
of the generated update SQL script - and a change like this would make 
it an almost impossible task :(


ATB,

Mark.

-- 
Mark Cave-Ayland - Senior Technical Architect
PostgreSQL - PostGIS
Sirius Corporation plc - control through freedom
http://www.siriusit.co.uk
t: +44 870 608 0063