postgresql修改表结构报错

error: cannot alter type of column used by a view or rule

分析原因是由于在该表之上有建了视图或规则。

场景模拟:

新建一张表

create table ptemp.online_job_info

(etl_system char(3) check (etl_system ~ '^[A-Z0-9_]*$')

,etl_job varchar(50) check (etl_job ~ '^[A-Z0-9_]*$')

,txdate date

,operator_type char(2) check (operator_type in ('重跑','下线','新增','变更'))

,request_person_name varchar(100) check (request_person_name not in ('')) not null

,remark varchar(200)

,last_modify_date date

,insert_time timestamp with time zone

,unique(etl_system,etl_job,txdate,last_modify_date)

)

distributed by (etl_system,etl_job);

在表基础上建视图

create view ptemp.v_online_job_info as
select * from ptemp.online_job_info;

更新字段，复现报错

alter table ptemp.v_online_job_info alter column remark type varchar(300);

error: cannot alter type of column used by a view or rule

解决方案一:

1.先将依赖ptemp.online_job_info的视图删除掉

drop view ptemp.online_job_info;

2.修改ptemp.online_job_info表结构

alter table ptemp.v_online_job_info alter column remark type varchar(300);

修改成功。

3.把视图建回来

create view ptemp.v_online_job_info as

select * from ptemp.online_job_info;

完成，表结构修改完毕。

作为一个拥有灵魂的SQL boy ，怎么可能就这样解决问题。动作一点都不酷！！！

分析原因:方案一解决这种问题的前提是，被修改表的所有依赖于被修改表的视图或其它都十分清楚，能够被你全部列举出来;只要有一条漏掉，你就修改不成功。除非使用绝招:把表级联删除掉，然后重新建表建视图建规则;当然这种操作危险性可想而知，反正我是不会在生产上这么操作的。

解决方案二:

思路:

1.先从数据库系统中把目标表的所有上游视图规则什么的全部找出来(而且不止一层，要递归查出所有)，并找一张表保存起来;

2.按照递归出来的层级关系，从最后一层开始一个一个删除掉;

3.修改目标表结构;

4.按照第一步保存的层级关系，把所有依赖按照层级关系，从第一层一个一个把视图规则等建回来。

实践操作:

1.建表。该表的作用是为了把等下查依赖关系的视图相关信息及建视图删视图预计保存下来。

create table ptemp.drop_create_view_sql_tmp(

parent_oid oid

,parent_table varchar(200)

,childer_oid oid

,childer_table varchar(200)

,drop_view_sql text

,create_view_sql text

,"level"" interger

) distributed by (parend_oid);

2.创建第一个函数。从系统中获取表上游所有相关信息

create or replace function ptemp.alter_table1(schema_name varchar(20),table_name varchar(100)) returns void

as

$$

declare

  v_table_name varchar(150);

  drop_view_cursor  cursor for select drop_view_sql from ptemp.drop_create_view_sql_tmp order by "level" desc;

  v_drop_view text;

begin

 v_table_name := schema_name || '.' || table_name;

 delete from ptemp.drop_create_view_sql_tmp;

 insert into ptemp.drop_create_view_sql_tmp 

 wirh recursive rectbl (parent_oid,childer_oid,level) as (

select dep.parent_oid,rwr.ev_class childer_oid,1 from (select refobjid parent_oid,objid from pg_depend dep1

where dep1.refobjid = v_table_name::regclass

and dep1.deptype = 'n'

and dep1.classid = 'pg_rewrite'::regclass

group by refobjid,objid ) dep

 inner join pg_rewrite rwr

on dep.objid = rwr.oid

where dep.parent_oid <> rwr.ev_class

union all

 select b.refobjid,c.ev_class,level+1

 from rectbl a

 inner join pg_depend b

 on a.childer_oid = b.b.refobjid

inner join pg_rewrite c

 on b.objid = c.oid

 where a.childer_oid <> c.ev_class

)

select parent_oid

           ,parent_oid::regclass parent_table

           ,childer_oid

           ,childer_oid::regclass childer_table

           ,'DROP VIEW ' || childer_oid::regclass || ';'  drop_view_sql

          ,'CREATE VIEW ' || childer_oid::regclass || ' AS' || chr(13) || pg_get_viewdef(childer_oid,true) as view_def

         ,level

from rectbl group by parent_oid,childer_oid,level;

 

----drop_view_cursor

open drop_view_cursor;

loop

FETCH drop_view_cursor INTO v_drop_view;

EXIT WHEN NOT FOUND;

execute v_drop_view;

end loop;

close drop_view_cursor;

end

$$

LANGUAGE plpgsql;

3.创建第二个函数。用于修改表结构化恢复依赖关系

create or replace function ptemp.alter_table2()

returns void

as

$$

declare

 create_view_cursor cursor for select create_view_sql from ptemp.drop_create_view_sql_tmp order by "level" ;

 v_create_view text;

begin

----create_view_cursor

 open create_view_cursor;

loop

FETCH create_view_cursor INTO v_create_view;

EXIT WHEN NOT FOUND;

execute v_create_view;

end loop;

close create_view_cursor;

end

$$

LANGUAGE plpgsql;

测试:

 1.备份依赖关系

select * from ptemp.alter_table1('ptemp','online_job_info');

2.真正修改表结构

alter table ptemp.v_online_job_info alter column remark type varchar(300);

3.还原表视图依赖

select * from ptemp.alter_table2();

当然也可以查看上次修改表的相关依赖信息:

select * from ptemp.drop_create_view_sql_tmp;

到这就结束了。

如果特别自信修改表结构的语句没有问题，是可以把第一个函数和第二个函数合并成一个函数，增加一个传递修改语句的参数进去就可，这样就能过一句执行就行，而不是分三步。

本人自己是建了一步修改表结构函数的，但是平时用的最多的还是方案二的三步曲。