On Mon, 2009-06-01 at 13:26 -0700, Josh Berkus wrote: > All, > > I just realized that even if you do this: > > table foo ( > id serial, > bar varchar(200) > ) > > ALTER TABLE foo ALTER COLUMN bar TYPE VARCHAR(1000) > > ... it triggers a heap & index rebuild, even though it's completely > unnecessary. Is this a special case of VARCHAR, or are there other > types where we should be allowing typemod changes without rebuilding?
NUMERIC(x, y) comes to mind, although that might be a more dangerous case. If you turn a NUMERIC(5,0) into a NUMERIC(5,1), then '1.2' may be stored as either '1' or '1.2' depending on whether you did the insert before or after the change. That's because, with NUMERIC, it's not really a constraint, but a rule for rounding. There may be other interesting cases involving constraints. For instance, if you have CHECK(i < 200), you should be able to add CHECK(i < 1000) without an exclusive lock or recheck. Then, with an exclusive lock, you can remove the original tighter constraint, but at least it wouldn't have to recheck the entire table. Not sure how much effort that is worth -- VARCHAR and NUMERIC typmods are probably more common problems and easier to fix. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers