Re: Suggested TODO: allow ALTERing of typemods without heap/index rebuild

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