[postgis-devel] In-place upgrade to 2.0
Paul Ramsey
pramsey at cleverelephant.ca
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
More information about the postgis-devel
mailing list