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

Josh Berkus <josh(at)agliodbs(dot)com> writes:
> I just realized that even if you do this:
> ALTER TABLE foo ALTER COLUMN bar TYPE VARCHAR(1000)
> ... it triggers a heap & index rebuild, even though it's completely
> unnecessary.

Yeah, this has been discussed before; I think it's even in the TODO
list. The stumbling block has been to identify a reasonably clean way
of determining which datatype changes don't require a scan.

> Is this a special case of VARCHAR, or are there other
> types where we should be allowing typemod changes without rebuilding?

There are any number of other cases where it's potentially interesting.
Consider:

* NUMERIC -> NUMERIC with a larger precision and/or scale

* VARBIT lengthening

* TIMESTAMP precision increase

* VARCHAR(anything) -> TEXT

and that's without considering the potential uses for user-defined
types. Now that we allow user-defined types to have usable typmods,
I'm sure there will be applications for them too. There are also
cases where a change might require a scan to ensure a new constraint
is met, but not a rewrite (eg, reducing the max length of VARCHAR).

We could certainly put in a quick hack that just covered a few of the
cases for built-in types, but it's not very pleasing ...

regards, tom lane