Re: Resize numeric column without changing data?

On Mon, Apr 9, 2012 at 9:57 AM, Lukas Eklund <lukas@eklund.io> wrote:
> Is it safe to resize a numeric column using in pg_attribute without
> altering the table?
>
> I have a large table (over 900 million rows) that, due to some poor
> design choices years ago, has a column numeric(8,2) that needs to
> modified to numeric(12,2). I would like to avoid running an ALTER
> TABLE statement on the table because of the time involved and the
> number of views I would have to drop and recreate. I'm running 8.3.
>
> I know that for varchar, it's possible to change the size of the
> column by updating atttypmod in pg_attribute for the column. What I'm
> wondering is if I can do the same thing for a numeric column? My
> understanding is that numeric is stored without any null padding so I
> was thinking this might work. There are no indexes on the column in
> question and the table is not partitioned.
>
> I've done some testing with a test table to see what happens and
> everything seems to work okay but I wanted to make sure I'm not
> missing something fundamental.
>
> This is a test table with a million rows:
>
> \d tester
>       Table "public.tester"
>  Column |     Type     | Modifiers
> --------+--------------+-----------
>  num    | numeric(8,2) |
>
> select min(num), max(num) from tester;
>   min    |    max
> ----------+-----------
>  -8000.00 | 999999.99
>
> UPDATE pg_attribute SET atttypmod = 786438
> WHERE attrelid = 'tester'::regclass
> AND attname = 'num';
> UPDATE 1
> Time: 32.895 ms
>
> \d tester
>      Table "public.tester"
>  Column |     Type      | Modifiers
> --------+---------------+-----------
>  num    | numeric(12,2) |
>
> Selects, inserts, and updates on the table all seem to work fine. I
> created some test views that rely on the test table and they seem to
> work fine as well. Are there any caveats I'm missing? Is there
> something about how numeric is stored on disk that I need be concerned
> about when altering the column in this method? Is it safe to just
> alter the metadata?

One thing that's pretty weird is that dependent view columns keep the
old typmod.  That might cause some issues with things that depend on
that value, for example client side tools.  But you can always change
those as well.

I think this might work because the numeric storage doesn't change for
this case.  But it's still quite dangerous and avoiding view
re-recreation is a pretty weak justification to go be updating system
catalogs.  You'd be better off spending some time developing a script
to recreate views. The only reason why I would personally be
considering this would be to avoid issues stemming from having to take
out a lock on the table and I would be testing very, very carefully if
I did so.

merlin