[postgis-devel] In-place upgrade to 2.0

Tue May 24 17:55:26 PDT 2011

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