Re: ALTER TYPE 3: add facility to identify further no-work cases

On Wed, Jan 26, 2011 at 07:44:43PM -0500, Tom Lane wrote:
> > numeric(8,2) -> numeric(7,2)
> > varbit(8) -> varbit(7)
> > text -> xml
>
> But how often do those really come up?

I'll speak from my own experience, having little idea of the larger community
experience on this one. I usually don't even contemplate changes like this on
nontrivial tables, because the pain of the downtime usually won't make up for
getting the schema just like I want it. Cases that I can't discard on those
grounds are fairly rare. As an order-of-magnitude estimate, I'd throw out one
instance per DBA-annum among the DBAs whose work I witness.

> And do you really save that
> much? The table still has to be locked against other users, so you're
> still down, and you're still doing all the reads and computation. I
> don't deny that saving the writes is worth something; I just don't agree
> that it's worth the development and maintenance effort that such a wart
> is going to cost us. User-exposed features are *expensive*.

If you have no indexes, you still save 50-75% of the cost by just reading and
computing, not rewriting. Each index you add, even if it doesn't involve the
column, pushes that advantage even further. With a typical handful of indexes,
a 95+% cost savings is common enough.

If we implemented ALTER TABLE ... SET DATA TYPE ... IMPLICIT, I'd agree that the
marginal value of automatically detecting the above three cases would not
justify the cost.