[postgis-devel] [PostGIS] #202: upgrade issues

[Paragon Corporation]

Strk,

To clarify.  Its always doing a create replace of all functions.

The issue I was trying to resolve are cases where you can't do a create
replace

1) Creating of casts -- we happened to miss one in our 1.4 upgrade script
for example -- so this version will do something like a 

SELECT postgis_create('CAST', 'geometry');

Which basically creates the casts needed for geometry if each does not exist

2) Create aggregates -- we always do a drop if exists and create aggregate
(from 1.4 moving forward even for new install).  I left that as is but
pulled that into the 
postgis_create function and the related functions that go with it.

So create aggregate statements get replaced with a 
SELECT postgis_create('AGGREGATE', 'makeline');  //which creates
ST_MakeLine, MakeLine aggs etc.

The reason I pushed it into the postgis_create even though it is doing the
same thing, is that dropping an aggregate is annoying and at some point
would like to do a conditional drop.  That's one of the reasons I don't like
pointless upgrades.  At some point I would like to have it check the version
being upgraded and conditionally drop if it has changed since then.  The
reason I don't like the DROP AGGRE approach we are currently using is that
for example I have some databases that use aggregates in views.  In such
cases the upgrade script fails.  Why should I have to rebuild my aggregate
views if I am moving from 1.4 to 1.5 or 1.4 to 1.4.1 for example.

3) Same issue with types, we kind of hard code the types we have changed, in
the postgis_upgrade.pl, but if I am going from 1.4 to 1.4.1
box3d_extent already exists, so upgrade fails.  So by doing a 

SELECT postgis_create('TYPE', 'box3d_extent');

It will only create the type if it doesn't exist in the database already.

--

Right now as a side effect, the postgis.sql I am proposing is a one size
fits all (it works for both upgrade and new install).  Mark had issue with
that, and I'm okay with having two -- and each having a slightly different
version of postgis_create plpgsql function if need be.

In the end in either way -- the postgis_create function is dropped when the
script is done.

Thanks,
Regina 

-----Original Message-----
From: postgis-devel-bounces at postgis.refractions.net
[mailto:postgis-devel-bounces at postgis.refractions.net] On Behalf Of strk
Sent: Tuesday, September 01, 2009 3:17 PM
To: PostGIS
Cc: postgis-devel at postgis.refractions.net
Subject: Re: [postgis-devel] [PostGIS] #202: upgrade issues

On Tue, Sep 01, 2009 at 04:05:36PM -0000, PostGIS wrote:

>  This does what I described above, but I tested it by upgrading a 
> postgis
>  1.4 to 1.5 and creating a postgis 1.5 fresh.  Have to find my old 1.3 
> data  to test upgrading, but should work since it recreates 
> aggregates, but only  creates types if they don't already exist.

What if they exist but have been obsoleted by later "bug-fixed" ones ?
That's the point of a "CREATE OR REPLACE" call after all.

--strk;

 Free GIS & Flash consultant/developer      ()  ASCII Ribbon Campaign
 http://foo.keybit.net/~strk/services.html  /\  Keep it simple! 
_______________________________________________
postgis-devel mailing list
postgis-devel at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-devel