Resize numeric column without changing data?
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?
Thanks,
--
Lukas Eklund
lukas@eklund.io
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.99UPDATE 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
On Mon, Apr 9, 2012 at 2:51 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
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?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
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!
Lukas
On Mon, Apr 9, 2012 at 3:06 PM, Lukas Eklund <lukas@eklund.io> wrote:
On Mon, Apr 9, 2012 at 2:51 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
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?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
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!
np -- note 9.2 will be much smarter about this:
http://archives.postgresql.org/message-id/20120101003619.GA4395@tornado.leadboat.com
merlin
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.
Regards,
Jeff Davis
On Mon, Apr 9, 2012 at 5:16 PM, Jeff Davis <pgsql@j-davis.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.
nope -- see link upthread. numeric is 9.2+
merlin
On Mon, Apr 9, 2012 at 6:16 PM, Jeff Davis <pgsql@j-davis.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