Skip site navigation (1) Skip section navigation (2)

[SOLVED] Re: Altering a column type - Most efficient way

From: Ow Mun Heng <Ow(dot)Mun(dot)Heng(at)wdc(dot)com>
To: Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>
Cc: Alvaro Herrera <alvherre(at)commandprompt(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Pgsql performance <pgsql-performance(at)postgresql(dot)org>, Mario Weilguni <mweilguni(at)sime(dot)com>
Subject: [SOLVED] Re: Altering a column type - Most efficient way
Date: 2008-07-14 01:46:46
Message-ID: 1216000006.22806.9.camel@neuromancer.home.net (view raw or flat)
Thread:
Lists: pgsql-performance
On Fri, 2008-07-11 at 09:55 -0500, Kevin Grittner wrote:
> > Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> >>> Ow Mun Heng wrote:
> >>> If it were this simple a change, I'm not certain why (I believe) PG
> >>>is checking each and every row to see if it will fit into the new column
> >>> definition/type. 
> >Because the code that does the ALTER TYPE is very generic, and it
> > doesn't (yet) have an optimization that tells it to skip the check
> > and the possible table rewrite in the cases where it's obviously not
> >needed(like this one).

>  If there's some low-hanging fruit here, +1 for getting that.  

I just tested this out and everything seems to be working fine. (cross
fingers - for now and if I do report back, it means we've crashed and
burned, but as of now... the low hanging fruit is tasty)

This 2 sec change is much preferred over the 3+ hour per table.

I agree with Tom that this is not useful in _all_ cases and may seem to
look like a hack, but it really isn't. Given that the condition that
we're expaning the min length rather than the opposite, it should be
pretty safe.


Guys(/gals) Thanks very much for brightening up a dreadry Monday
morning.


In response to

pgsql-performance by date

Next:From: RamasubramanianDate: 2008-07-14 05:59:24
Subject: Trigger is taking time to fire
Previous:From: Oleg BartunovDate: 2008-07-12 14:30:44
Subject: Re: how to estimate shared_buffers...

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group