openGauss 修改表字段类型 —— 视图依赖批量处理手册

适用场景：openGauss（PostgreSQL 血统）中修改某张表的字段类型时，报错
ERROR: cannot alter type of a column used by a view or rule，
必须先删除所有依赖该表的视图、改完表后再重建。
本手册提供一套批量处理流程，无需逐个排查视图。

1. 为什么会这样（原理）

简单说：openGauss 是"宁可提前拦住你，也不让视图悄悄坏掉"；MySQL 是"先让你改，坏了运行时再说"（MySQL 里把字段删了视图同样会挂，只是报错时机晚）。这是设计取舍，不是缺陷。

2. 整体流程

第 1 步 秒级预检：数一下有多少视图依赖这张表 第 2 步 查出所有依赖视图，定义备份到一张表 第 3 步 删视图 → 改表 → 按序重建（一个 DO 块，失败整体回滚） 第 4 步 验证 + 补授权

以下示例：把表t_organ的字段org_name改为varchar(100)。
执行前替换成自己的表名 / 字段名 / 目标类型。

性能注意（重要）

递归查询里不要在递归项中携带 depth 列：UNION去重的是整行，
带 depth 会导致同一视图以不同层级反复传播，视图间路径一多行数成倍膨胀，
且每轮 UNION 都要对累计结果排序去重，核心表（如组织机构表，被全库视图引用）
会慢到不可用。正确做法：递归只收集去重后的 viewoid；
重建顺序改用view_oid（OID 随创建递增，依赖者一定晚于被依赖者创建）

• DO 块内多轮重试兜底。

3. 第 1 步：秒级预检

WITHRECURSIVE dep(viewoid)AS(SELECTr.ev_classFROMpg_depend dJOINpg_rewrite rONr.oid=d.objidJOINpg_class tONt.oid=d.refobjidWHEREd.classid='pg_rewrite'::regclassANDd.refclassid='pg_class'::regclassANDt.relname='t_organ'-- ★ 表名UNIONSELECTr.ev_classFROMdepJOINpg_depend dONd.refobjid=dep.viewoidANDd.classid='pg_rewrite'::regclassANDd.refclassid='pg_class'::regclassJOINpg_rewrite rONr.oid=d.objid)SELECTcount(*)AS依赖视图总数FROMdep;

正常应几秒内出结果（几十上百个视图都正常，核心表会更多）。
如果连这个都慢，用EXPLAIN ANALYZE分析后另查原因（库对象过多等）。

4. 第 2 步：备份依赖视图定义

CREATETABLEview_bak_20260910AS-- ★ 备份表名（带日期）WITHRECURSIVE dep(viewoid)AS(SELECTr.ev_classFROMpg_depend dJOINpg_rewrite rONr.oid=d.objidJOINpg_class tONt.oid=d.refobjidWHEREd.classid='pg_rewrite'::regclassANDd.refclassid='pg_class'::regclassANDt.relname='t_organ'-- ★ 表名UNIONSELECTr.ev_classFROMdepJOINpg_depend dONd.refobjid=dep.viewoidANDd.classid='pg_rewrite'::regclassANDd.refclassid='pg_class'::regclassJOINpg_rewrite rONr.oid=d.objid)SELECTn.nspnameASview_schema,v.relnameASview_name,v.oidASview_oid,-- 创建顺序，重建时按它排pg_get_viewdef(v.oid,true)ASview_def-- ★ 完整视图定义，自动备份FROMdepJOINpg_class vONv.oid=dep.viewoidANDv.relkind='v'JOINpg_namespace nONn.oid=v.relnamespace;-- 查看备份结果（0 行 = 没有视图依赖这张表，直接 ALTER 即可，不用跑脚本二）SELECTview_schema,view_nameFROMview_bak_20260910ORDERBYview_oid;

说明：

• 递归 CTE 会连带查出视图套视图（A 视图用 B 视图，B 视图用目标表），无需逐个排查。
• view_def列就是每个视图的完整 SELECT 语句，备份到此完成，不存在丢定义的风险。
• 同名表存在于多个模式时，在t.relname = '...'处追加AND tn.nspname = '模式名'（需额外 JOIN pg_namespace）。

5. 第 3 步：删视图 → 改表 → 重建（一个 DO 块，失败自动整体回滚）

设计要点：备份单独执行（第 2 步），删/改/建合成一个 DO 块。
DO 块失败会整体回滚，若备份也放在里面，回滚时备份表会一起消失；
分开执行才能保证任何失败发生后备份都还在。

DO$$DECLAREv_schematext:='public';-- ★ 表所在模式v_tabletext:='t_organ';-- ★ 表名（与备份一致）v_columntext:='org_name';-- ★ 字段名v_newtypetext:='varchar(100)';-- ★ 目标类型rec record;v_pendingint;v_roundint:=0;BEGIN-- 1. 删除所有依赖视图（CASCADE 连带套娃视图，顺序无关）FORrecINSELECT*FROMview_bak_20260910LOOPEXECUTEformat('DROP VIEW %I.%I CASCADE',rec.view_schema,rec.view_name);RAISE NOTICE'DROP %',rec.view_name;ENDLOOP;-- 2. 修改字段类型-- ★ 不兼容转换（如 varchar→int）时，把本行改成带 USING 的写法：-- EXECUTE format('ALTER TABLE %I.%I ALTER COLUMN %I TYPE int USING %I::int', ...)EXECUTEformat('ALTER TABLE %I.%I ALTER COLUMN %I TYPE %s',v_schema,v_table,v_column,v_newtype);RAISE NOTICE'ALTER OK';-- 3. 按创建顺序（view_oid）重建；个别因顺序问题建失败的，下一轮自动重试LOOPv_round :=v_round+1;v_pending :=0;FORrecINSELECT*FROMview_bak_20260910ORDERBYview_oidLOOPBEGINEXECUTEformat('CREATE VIEW %I.%I AS %s',rec.view_schema,rec.view_name,rec.view_def);EXCEPTIONWHENundefined_tableORundefined_columnORundefined_functionORundefined_objectTHENv_pending :=v_pending+1;-- 依赖还没建好，下轮再试END;ENDLOOP;EXITWHENv_pending=0ORv_round>=20;ENDLOOP;IFv_pending>0THENRAISE EXCEPTION'仍有 % 个视图重建失败，请检查备份表中的定义',v_pending;ENDIF;RAISE NOTICE'════════ 全部完成（共 % 轮）════════',v_round;END$$;

执行时看 NOTICE 输出，最后看到"全部完成"才算成功；中途报错直接整体回滚，
视图全部自动回来，什么都不用补救。这是 openGauss（PG 血统）比 MySQL 强的地方：
DDL 可以回滚（MySQL 的 DDL 是隐式提交，做不到）。

注意事项：

• ALTER TYPE期间会锁表，放业务低峰期执行。
• 涉及表重写的类型转换（如 int → text）锁表时间更长，提前评估窗口。
• 重建必须用CREATE VIEW，不要用CREATE OR REPLACE VIEW——改了字段类型后
  视图输出列的类型也变了，OR REPLACE不允许改变视图列类型会报错。

6. 第 4 步：验证 + 补授权

-- 1) 视图数量对得上SELECTcount(*)FROMview_bak_20260910;-- 备份了多少个，重建后就应该有多少个-- 2) 抽查视图能正常查询SELECT*FROM某个重建的视图LIMIT1;

如果这些视图上单独GRANT过权限，重建后要重新授权（备份的定义里不包含权限）。
需要同时备份权限的话，第 2 步 SELECT 里多加一列v.relacl。

7. 其他注意事项

1. 物化视图：备份查询只查了relkind = 'v'（普通视图）。如果库里还有依赖目标表的
   物化视图（relkind = 'm'），同样会被 ALTER 拦下，处理方式相同（把过滤条件改为'm'，
   保存定义用同样的pg_get_viewdef，重建后记得REFRESH MATERIALIZED VIEW）。
2. 函数/存储过程：PL/pgSQL 函数体以文本存储，不参与依赖检查，不受影响；但若函数
   中SELECT了类型变更的列并在体内做了强类型操作，需自行回归测试。
3. 确认无误后清理：操作完成并稳定运行一段时间后，可删除备份表
   DROP TABLE view_bak_20260910;（建议至少保留一个迭代周期）。
4. 备选导出方式：gs_dump -d 库名 -s -f schema.sql导出全库结构，其中的 CREATE VIEW
   天然按依赖顺序排好，可从中截取需要的部分执行；Data Studio / DBeaver 里右键视图导出 DDL 亦可。
5. A（Oracle）兼容模式：若DO $$语法报错，把脚本改成
   CREATE OR REPLACE PROCEDURE ...; CALL ...即可，逻辑不变。

8. 速查卡片（Copy-Paste 版）

-- ① 预检（秒级，只数个数）WITHRECURSIVE dep(viewoid)AS(SELECTr.ev_classFROMpg_depend dJOINpg_rewrite rONr.oid=d.objidJOINpg_class tONt.oid=d.refobjidWHEREd.classid='pg_rewrite'::regclassANDd.refclassid='pg_class'::regclassANDt.relname='t_organ'UNIONSELECTr.ev_classFROMdepJOINpg_depend dONd.refobjid=dep.viewoidANDd.classid='pg_rewrite'::regclassANDd.refclassid='pg_class'::regclassJOINpg_rewrite rONr.oid=d.objid)SELECTcount(*)FROMdep;-- ② 备份（改表名）CREATETABLEview_bak_YYYYMMDDASWITHRECURSIVE dep(viewoid)AS(SELECTr.ev_classFROMpg_depend dJOINpg_rewrite rONr.oid=d.objidJOINpg_class tONt.oid=d.refobjidWHEREd.classid='pg_rewrite'::regclassANDd.refclassid='pg_class'::regclassANDt.relname='t_organ'UNIONSELECTr.ev_classFROMdepJOINpg_depend dONd.refobjid=dep.viewoidANDd.classid='pg_rewrite'::regclassANDd.refclassid='pg_class'::regclassJOINpg_rewrite rONr.oid=d.objid)SELECTn.nspnameASview_schema,v.relnameASview_name,v.oidASview_oid,pg_get_viewdef(v.oid,true)ASview_defFROMdepJOINpg_class vONv.oid=dep.viewoidANDv.relkind='v'JOINpg_namespace nONn.oid=v.relnamespace;-- ③ 删 → 改 → 重建（改 DECLARE 里的 4 个 ★ 变量后整块执行）DO$$DECLAREv_schematext:='public';v_tabletext:='t_organ';v_columntext:='org_name';v_newtypetext:='varchar(100)';rec record;v_pendingint;v_roundint:=0;BEGINFORrecINSELECT*FROMview_bak_YYYYMMDDLOOPEXECUTEformat('DROP VIEW %I.%I CASCADE',rec.view_schema,rec.view_name);ENDLOOP;EXECUTEformat('ALTER TABLE %I.%I ALTER COLUMN %I TYPE %s',v_schema,v_table,v_column,v_newtype);LOOPv_round :=v_round+1;v_pending :=0;FORrecINSELECT*FROMview_bak_YYYYMMDDORDERBYview_oidLOOPBEGINEXECUTEformat('CREATE VIEW %I.%I AS %s',rec.view_schema,rec.view_name,rec.view_def);EXCEPTIONWHENundefined_tableORundefined_columnORundefined_functionORundefined_objectTHENv_pending :=v_pending+1;END;ENDLOOP;EXITWHENv_pending=0ORv_round>=20;ENDLOOP;IFv_pending>0THENRAISE EXCEPTION'仍有 % 个视图重建失败',v_pending;ENDIF;RAISE NOTICE'全部完成';END$$;-- ④ 验证 / 补授权 / 稳定后 DROP TABLE view_bak_YYYYMMDD

9 直接提供创建函数

-- =====================================================================-- 修改被视图引用的表字段类型 - 公共函数(自动删除并重建依赖视图)-- 变更集: m1-erpServiceRight-3.1.10-20261104099900---- 使用示例(各模块 liquibase / 手工变更时直接调用):-- SELECT public.fn_alter_column_type('right','t_organ','org_name','varchar(100)');-- SELECT public.fn_alter_column_type('material','ms_xxx','amt','numeric(18,2)','amt::numeric');---- 依赖视图查询(仅查询、不动任何对象, 可单独用于排查):-- SELECT * FROM public.fn_list_view_deps('right','t_organ');---- 设计说明:-- 1. 依赖查找基于 pg_depend + pg_rewrite 递归, 数据库级生效, 天然支持-- 跨 schema(schema A 的视图引用 schema B 的表)与多层嵌套视图-- 2. 函数运行期间强制 search_path = pg_catalog, pg_temp:-- pg_get_viewdef 因此输出全限定名(如 FROM right.t_organ), 重建视图时-- 不受调用会话 search_path 影响, 杜绝"裸表名被别的 schema 同名表-- 静默顶替"的绑错事故(本库为多 schema 单库, 同名表风险真实存在)-- 3. 嵌套视图无法预知重建先后顺序: 采用"失败留到下一轮重试"循环,-- 最多 20 轮, 仍未成功则抛错(liquibase 事务内整体回滚)-- 4. 幂等: 目标类型与当前类型一致时直接返回 0, 不动任何视图;-- 类型比较通过临时表探测归一化(数据库自己解析 p_newtype 得到-- format_type 规范名), 'varchar(100)' 与 'character varying(100)'-- 视为同一类型-- 5. 物化视图(relkind='m')不自动重建(重建即丢数据), 检测到即抛错,-- 请人工处理-- 6. 视图的 COMMENT/GRANT/属主信息会随重建丢失, 有此类视图需事后补授权-- 7. 列被外键/唯一约束等对象引用时, 需在 p_using 中给出转换并用-- ALTER ... CASCADE 语义自行评估, 本函数不自动级联约束---- openGauss 5.0 兼容性说明(生产库为 openGauss 5.0.0, 语法按其限制书写,-- 同写法在 PostgreSQL 9.2+ 均兼容):-- a. 函数 SET 子句的值必须写成带引号的字符串 'pg_catalog, pg_temp',-- 裸标识符列表(SET search_path = pg_catalog, pg_temp)会解析报错-- b. 不使用 to_regclass(openGauss 无此函数), 表存在性走-- pg_class + pg_namespace 联查-- c. plpgsql 的 SELECT INTO 在 openGauss 上"无结果即抛错"(不同于-- PostgreSQL 的置 NULL), 统一用 max() 聚合保证恒返回一行-- d. 临时表 DROP/引用不带 pg_temp 前缀(openGauss 限制对-- pg_temp.xxx 前缀的访问)---- 幂等/升级说明:-- * 函数使用 CREATE OR REPLACE, 已存在时整体替换为本文件定义, 重复-- 执行/失败重试/手工预建过均不会报错且强制对齐版本, 故刻意不加-- "函数已存在则跳过"的前置判断-- * 需要修改函数逻辑时: 新增 changeSet 重新 CREATE OR REPLACE,-- 勿改本文件(已发布的变更集受 checksum 锁定)-- =====================================================================-- 依赖视图清单(只读): 返回直接与间接依赖指定表/物化视图的全部视图CREATEORREPLACEFUNCTIONpublic.fn_list_view_deps(p_schema name,p_table name)RETURNSTABLE(view_oid oid,view_schema name,view_name name,view_kind"char",view_deftext)LANGUAGEsqlSTABLESETsearch_path='pg_catalog, pg_temp'AS$$WITHRECURSIVE depAS(SELECTr.ev_classASdep_oidFROMpg_depend dJOINpg_rewrite rONr.oid=d.objidWHEREd.classid='pg_rewrite'::regclassANDd.refclassid='pg_class'::regclassANDd.refobjid=(SELECTc.oidFROMpg_class cJOINpg_namespace nONn.oid=c.relnamespaceWHEREn.nspname=p_schemaANDc.relname=p_tableLIMIT1)UNIONSELECTr.ev_classFROMdepJOINpg_depend dONd.refobjid=dep.dep_oidANDd.classid='pg_rewrite'::regclassANDd.refclassid='pg_class'::regclassJOINpg_rewrite rONr.oid=d.objid)SELECTv.oidASview_oid,n.nspnameASview_schema,v.relnameASview_name,v.relkindASview_kind,pg_get_viewdef(v.oid,true)ASview_defFROMdepJOINpg_class vONv.oid=dep.dep_oidANDv.relkindIN('v','m')JOINpg_namespace nONn.oid=v.relnamespaceWHEREn.nspnameNOTLIKE'pg\_%'ANDn.nspname<>'information_schema'$$;-- 修改列类型并自动重建依赖视图: 返回成功重建的视图数量(幂等重跑返回 0)CREATEORREPLACEFUNCTIONpublic.fn_alter_column_type(p_schema name,p_table name,p_column name,p_newtypetext,p_usingtextDEFAULTNULL)RETURNSintLANGUAGEplpgsqlSETsearch_path='pg_catalog, pg_temp'AS$$DECLAREr record;v_table_oid oid;v_oldtypetext;v_newtypetext;v_matviewstext;v_sqltext;v_doneint:=0;v_pendingint;v_roundint;BEGIN-- 1. 表/列存在性检查(max() 聚合恒返回一行, 规避 openGauss "SELECT INTO 无行抛错")SELECTmax(c.oid)INTOv_table_oidFROMpg_class cJOINpg_namespace nONn.oid=c.relnamespaceWHEREn.nspname=p_schemaANDc.relname=p_table;IFv_table_oidISNULLTHENRAISE EXCEPTION'表 %.% 不存在',p_schema,p_table;ENDIF;SELECTmax(format_type(a.atttypid,a.atttypmod))INTOv_oldtypeFROMpg_attribute aWHEREa.attrelid=v_table_oidANDa.attname=p_columnANDNOTa.attisdropped;IFv_oldtypeISNULLTHENRAISE EXCEPTION'列 %.%.% 不存在',p_schema,p_table,p_column;ENDIF;-- 2. 幂等判断: 用临时表让数据库解析 p_newtype, 得到与 v_oldtype 同源的-- format_type 规范名后严格比较(非法类型名也会在此提前报错)DROPTABLEIFEXISTS_type_probe;EXECUTEformat('CREATE TEMP TABLE _type_probe(col_probe %s)',p_newtype);SELECTformat_type(a.atttypid,a.atttypmod)INTOv_newtypeFROMpg_attribute aWHEREa.attrelid='_type_probe'::regclassANDa.attname='col_probe';DROPTABLE_type_probe;IFv_oldtype=v_newtypeTHENRETURN0;ENDIF;-- 3. 物化视图依赖: 不自动重建(重建即丢数据), 交人工处理SELECTstring_agg(view_schema||'.'||view_name,', ')INTOv_matviewsFROMpublic.fn_list_view_deps(p_schema,p_table)WHEREview_kind='m';IFv_matviewsISNOTNULLTHENRAISE EXCEPTION'表 %.% 上存在物化视图: %, 物化视图不自动重建(数据会丢失), 请手工处理',p_schema,p_table,v_matviews;ENDIF;-- 4. 依赖视图定义备份到临时表(含跨 schema 与嵌套视图)DROPTABLEIFEXISTS_dep_views;CREATETEMPTABLE_dep_viewsASSELECTview_oid,view_schema,view_name,view_defFROMpublic.fn_list_view_deps(p_schema,p_table);-- 5. 删除全部依赖视图(CASCADE 会连带删除嵌套依赖, 故补 IF EXISTS:-- 循环走到已被连带删除的视图时静默跳过)FORrINSELECTDISTINCTview_schema,view_nameFROM_dep_viewsLOOPEXECUTEformat('DROP VIEW IF EXISTS %I.%I CASCADE',r.view_schema,r.view_name);ENDLOOP;-- 6. 修改列类型(需要显式转换语义时传 p_using, 如 'amt::numeric')v_sql :=format('ALTER TABLE %I.%I ALTER COLUMN %I TYPE %s',p_schema,p_table,p_column,p_newtype);IFp_usingISNOTNULLANDp_using<>''THENv_sql :=v_sql||' USING '||p_using;ENDIF;EXECUTEv_sql;-- 7. 循环重建: 重建失败的(其依赖的视图尚未建回)留到下一轮, 最多 20 轮v_round :=0;LOOPv_round :=v_round+1;v_pending :=0;FORrINSELECT*FROM_dep_viewsORDERBYview_oidLOOPBEGINEXECUTEformat('CREATE VIEW %I.%I AS %s',r.view_schema,r.view_name,r.view_def);DELETEFROM_dep_viewsWHEREview_oid=r.view_oid;v_done :=v_done+1;EXCEPTIONWHENundefined_tableORundefined_columnORundefined_functionTHENv_pending :=v_pending+1;END;ENDLOOP;EXITWHENv_pending=0ORv_round>=20;ENDLOOP;DROPTABLE_dep_views;IFv_pending>0THENRAISE EXCEPTION'表 %.%.% 类型已改为 %, 但仍有 % 个视图重建失败(超过重试上限), 请人工检查',p_schema,p_table,p_column,p_newtype,v_pending;ENDIF;RETURNv_done;END$$;