Обсуждение: Why cannot alter column type when a view depends on it?

On Sun, Sep 28, 2025 at 4:19 PM Chao Li <li.evan.chao@gmail.com> wrote:
>
> ```
> evantest=# create table t (c char(20));
> CREATE TABLE
> evantest=# create view v_t as select * from t;
> CREATE VIEW
>
> evantest=# alter table t alter column c type char(25);
> ERROR:  cannot alter type of a column used by a view or rule
> DETAIL:  rule _RETURN on view v_t depends on column "c"
> ```
>
> I tried to understand why this restriction is set, then I found that, when a function uses a view, the view can
actuallybe dropped, only when the function is executed, it will raise an error saying the view doesn’t exist. From this
perspective,I think we should allow alter column type when a view depends on the column. 
>

hi.

the simple case you mentioned above, it's definitely doable.
however say we have
create view v_t1 as select * from v_t;

RememberAllDependentForRebuilding can not cope with the dependency
between t and v_t1.
ATPostAlterTypeCleanup, we use DROP_RESTRICT in
performMultipleDeletions(objects, DROP_RESTRICT, PERFORM_DELETION_INTERNAL);

That means
``alter table t alter column c type char(25);``
can not drop and recreate view v_t1,
it will error out within performMultipleDeletions while trying to drop view v_t.

I tried to understand why this restriction is set, then I found that, when a function uses a view, the view can actually be dropped, only when the function is executed, it will raise an error saying the view doesn’t exist.

In summary, this email is just raising the issue rather than proposing a solution. I want to hear feedbacks from the community. If people are interested in a solution to remove the restriction, then I can spend time on it.

Chao Li <li.evan.chao@gmail.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,
theconstraints will become invalid, so that rebuilding the constant will fail, as a result, alter column type will fail
aswell. 

> So, while altering a column type, we can also rebuild depended views. If rebuilding views succeeds, then alter column
typealso 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