[postgis-devel] In-place upgrade to 2.0

Wed May 25 08:16:20 PDT 2011

The payoff would have been supporting the extension upgrade syntax in 9.1 for upgrade from 1.5 to 2.0

P.

On 2011-05-24, at 5:55 PM, "Paragon Corporation" <lr at pcorp.us> wrote:

> Thought about it more.  I don't think its worth it if you need to do a CAST.
> Such an alter column would require a table rewrite anyway so for a database
> that is mostly tables with geometry columns 
> I think it would still be faster to do a database restore. A database
> restore is the least of our problems anyway so I think time is better spent
> getting the rest of the missing pieces of PostGIS 2.0 in place.
> 
> Thanks,
> Regina
> 
> -----Original Message-----
> From: postgis-devel-bounces at postgis.refractions.net
> [mailto:postgis-devel-bounces at postgis.refractions.net] On Behalf Of Paragon
> Corporation
> Sent: Tuesday, May 24, 2011 8:37 PM
> To: 'PostGIS Development Discussion'
> Subject: Re: [postgis-devel] In-place upgrade to 2.0
> 
> Paul,
> 
> Here is a thought.  The only issue I think you'll have is with functions,
> operators, and views that use th geometry type.  So how about this
> 
> 1) Backup all functions, operators, views, constraints
> 2) Dump all functons, operators, views, constraints that use the geometry
> type
> 3) run your alter table alter column stuff
> 4) restore the dropped functions, operators, views, constraints (the ones
> that have the same name as what we have installed in PostGIS 2.0 will not
> come backup)
> 5) run the uninstall_legacy.sql I'm working on to get rid of legacy stuff we
> really don't want anymore.
> 
> 
> It's still not a trivial process and one that requires some testing to make
> sure we've dotted all our I's and crossed our T's.
> 
> Thanks,
> Regina
> 
> -----Original Message-----
> From: postgis-devel-bounces at postgis.refractions.net
> [mailto:postgis-devel-bounces at postgis.refractions.net] On Behalf Of Paul
> Ramsey
> Sent: Tuesday, May 24, 2011 4:08 PM
> To: PostGIS Development Discussion
> Subject: Re: [postgis-devel] In-place upgrade to 2.0
> 
> Doesn't look like its useful. We need something like cast, that will apply a
> function to the input to it goes in one way and comes out another. Another
> possibility would be to see if the binary signature of serialized_form is
> sufficiently distinct from gserialized that we can just transparently notice
> which form we're receiving and go from there in the deserialization
> function. Mmmm. Probably not, there's too many pieces of code that want to
> only partially deserialize for performance reasons and expect a certain disk
> layout.
> P.
> 
> On Tue, May 24, 2011 at 1:05 PM, Paul Ramsey <pramsey at opengeo.org> wrote:
>> I DID NOT KNOW THAT!
>> That's really excellent, and boy I wish I knew what file I should be 
>> reading to know all these new features... what file should I be 
>> reading?
>> I'll look at this and see if it's useful for our problem.
>> P.
>> 
>> On Tue, May 24, 2011 at 12:04 PM, Paragon Corporation <lr at pcorp.us> wrote:
>>> Yes you are correct -- it will bulk.  You have to drop the views 
>>> currently if you change any column used in a view.  The ability for a 
>>> view to automagically rebuild themselves has been on the plate for as 
>>> far back as I can remember and I don't think its funded yet and it 
>>> drives
> Leo crazy :).
>>> 
>>> However for 9.1 users, 9.1 supports ALTER TYPE  so you could just 
>>> change the TYPE underneath the hood so even if uses use views, it 
>>> should work to just ALTER the TYPE.  But of course you probably 
>>> already new that :)
>>> 
>>> Thanks,
>>> Regina
>>> 
>>> -----Original Message-----
>>> From: postgis-devel-bounces at postgis.refractions.net
>>> [mailto:postgis-devel-bounces at postgis.refractions.net] On Behalf Of 
>>> Paul Ramsey
>>> Sent: Tuesday, May 24, 2011 11:30 AM
>>> To: PostGIS Development Discussion
>>> Subject: Re: [postgis-devel] In-place upgrade to 2.0
>>> 
>>> yes, my scheme was going to be to two-step by first creating 
>>> GEOMETRY2, then altering all the columns to that (by maintaining an 
>>> lwgeom handler to do the cast from serialized_form to gserialized), 
>>> then creating GEOMETRY and altering all the columns to that. Which 
>>> would work fine for data-only databases, but installations with more 
>>> complex constructions (views,
>>> triggers) expecting 'geometry' input arguments might balk if I alter 
>>> their column types underneath them.
>>> 
>>> P.
>>> 
>>> On Mon, May 23, 2011 at 1:49 AM, Mark Cave-Ayland 
>>> <mark.cave-ayland at siriusit.co.uk> wrote:
>>>> On 21/05/11 10:56, Paul Ramsey wrote:
>>>> 
>>>>> The mechanism is the same for everyone, the 9.1 users just get it 
>>>>> "automagically" with a SQL command and everyone else has to (quell
>>>>> horror!) run a .sql file.
>>>>> 
>>>>> Though now that I think about it in the clear light of early 
>>>>> morning there's lots of ways my scheme will fail, so perhaps not.
>>>> 
>>>> I thought that in-place upgrade only works if the binary formats are 
>>>> unchanged? So if we change LWGEOM -> G_GEOMETRY then this isn't 
>>>> going to work. Or does the extension upgrade execute an ALTER COLUMN 
>>>> statement to rebuild the column anyway?
>>>> 
>>>> 
>>>> 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
>>>> 
>>>> Sirius Labs: http://www.siriusit.co.uk/labs 
>>>> _______________________________________________
>>>> postgis-devel mailing list
>>>> postgis-devel at postgis.refractions.net
>>>> http://postgis.refractions.net/mailman/listinfo/postgis-devel
>>>> 
>>> _______________________________________________
>>> postgis-devel mailing list
>>> postgis-devel at postgis.refractions.net
>>> http://postgis.refractions.net/mailman/listinfo/postgis-devel
>>> 
>>> 
>>> _______________________________________________
>>> postgis-devel mailing list
>>> postgis-devel at postgis.refractions.net
>>> http://postgis.refractions.net/mailman/listinfo/postgis-devel
>>> 
>> 
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-devel
> 
> 
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-devel
> 
> 
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-devel