Hi Hacker,
We have received a complain many times from our customers, especially those
switched to PG from Oracle, that when they need to alter a column’s type, if
there are views depend on the column, then users have to drop the view first
and recreate the view after altering the column type.
This is quite easy to reproduce:
```
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 actually be 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.
But I also realized the subtle complexities involved. For example, If a view is
defined with a where clause, for example:
```
# create view v_t2 as select * from t where c = ‘xx’;
```
Then if you alter type of c from char(20) to int, that will cause the select
statement invalid. However, alter table itself will block this type change,
because it cannot cast char(20) to int automatically unless “using” is
specified. So, at least for the following two cases:
* a view is defined with select only without where
* alter column type without using (the complexity is that even if “using” is
specified, it might still be safe)
“Alter column” can be safely done without checking if a view depends on it.
The checking comes from RemeberAllDependentForRebuilding(), and I see a code
comment:
case RewriteRelationId:
/*
* View/rule bodies have pretty much the same issues as
* function bodies. FIXME someday.
*/
if (subtype == AT_AlterColumnType)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cannot alter type of a column used by a
view or rule"),
errdetail("%s depends on column \"%s\"",
getObjectDescription(&foundObject,
false),
colName)));
break;
From this comment, I guess PG actually wants to remove the restriction.
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.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
