Thread: 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.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
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