Re: Why cannot alter column type when a view depends on it?

Chao Li <li(dot)evan(dot)chao(at)gmail(dot)com> writes:
> After researching, I think we can take the similar way against constant and index for view. After altering a column’s type, related constrains and indexes will be rebuilt. Column type change may also break constraints or indexes. For example, if an int typed column has a constraints of “check (a>0)”, then if you change the column type from int to text, the constraints will become invalid, so that rebuilding the constant will fail, as a result, alter column type will fail as well.

> So, while altering a column type, we can also rebuild depended views. If rebuilding views succeeds, then alter column type also succeeds. This should be a reliable solution.

I'm pretty uncomfortable with this proposal, because "this should be
a reliable solution" seems like very naive optimism. ALTER COLUMN
TYPE's ability to rebuild indexes and constraints has never been more
than an 80% kind of feature: there is a nontrivial risk of its doing
the wrong thing. Now, failing is not the wrong thing, but installing
a new object that doesn't act like the old object is. We are making
all kinds of assumptions when we do those rebuilds, such as that the
same function or operator name means the same thing for the new data
type.

We've accepted that hazard for indexes and constraints because
the downside risk seems limited. For instance, if we install an
expression index that isn't really what it should have been, the
worst case is that it won't match queries and hence won't be useful.
A bad constraint might prevent you from inserting valid data, but
when you notice that you can fix it.

However, I'm not convinced that the same argument applies to views.
People will be trying to use the rebuilt view, and if it doesn't do
what it should do, a real mess could ensue, such as silently-wrong
query results or table updates.

On top of that, rebuilding indexes and constraints is a one-level
problem: we might get them right or not, but nothing else depends on
it. With views, after you've rebuilt them (and potentially changed
their output data types), you then have to consider rebuilding views
that depend on the changed one, and so on for perhaps many levels.
Each level increases the risk of a bad deduction about what should
happen, which would then cascade to all subsequently-modified views.

So on the whole I think this would be an anti-feature. Given the
limited field demand for it, I'm not convinced we should put time
into it.

If we did want to pursue this further, I'd like to start by trying
to make the existing expression-rebuilding technology less of a
cross-your-fingers-and-pray matter. I'm not very sure what that
should look like, except that I'd like something better than
"operators with the same name probably do the same thing".
We invented the notions of operator classes and families so that
we could formalize reasoning of that sort for index-associated
operators. I wonder if some similar constructs could help?

regards, tom lane