Re: Resize numeric column without changing data?

On Mon, Apr 9, 2012 at 6:16 PM, Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
> On Mon, 2012-04-09 at 16:06 -0400, Lukas Eklund wrote:
>> Ah. I must have not noticed that the typmod for views is not inherited
>> automatically. I'm okay with developing a script to recreate the 15 or
>> so views the depend on that table. What I'm trying to avoid is locking
>> that table for a substantial amount of time. Thanks for the advice!
>
> FYI, later versions of postgres try to avoid rewrites of the table when
> possible for simple ALTERs like the one you're talking about.
>
> Are you using PostgreSQL 9.1? Try it out in a simple test case... maybe
> the lock is only held for an instant anyway. Some of these optimizations
> went in 9.2 (not released yet) but I think the one you need is in 9.1.

I'm using 8.3 and while I was searching the google machine for
information about resizing columns in this manner I came across the
patches for varchar and numeric that have made their way into 9.1 and
9.2. I'm using those as additional ammunition to speed the upgrade
from 8.3 to 9.1.

I've made the change in a development environment and all the
applications behave okay. I'm still testing stored procedures that use
the table and working out a comprehensive testing plan before I make
the change anywhere else.

Thanks,
Lukas