postgresql修改表结构报错

最新推荐文章于 2023-11-20 17:17:19 发布

通六

最新推荐文章于 2023-11-20 17:17:19 发布

阅读量985

点赞数

文章标签： postgresql 数据库运维

本文链接：https://blog.csdn.net/weixin_68383707/article/details/134376903

版权

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;