[postgis-devel] postgis_upgrade.sql

[Paragon Corporation]

I wasn't thinking of getting rid of the upgrade sql nor doing all the logic
in the upgrade_funciton.

What I was saying is we  have a plpgsql function that selective deletes new
functions -- like all the functions supporting box3d or whatever type we
created and throws and error if there is no way to do it without destroying
data or views.

Then we have a single script that upgrades (will support upgrade from
1.x----1.5.
Most of the time our upgrade is just a create or replace so its only in the
case of new types and possibly aggregates that there is an issue.

Thanks,
Regina 

-----Original Message-----
From: postgis-devel-bounces at postgis.refractions.net
[mailto:postgis-devel-bounces at postgis.refractions.net] On Behalf Of Mark
Cave-Ayland
Sent: Wednesday, June 24, 2009 12:22 PM
To: PostGIS Development Discussion
Subject: Re: [postgis-devel] postgis_upgrade.sql

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
_______________________________________________
postgis-devel mailing list
postgis-devel at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-devel