openGauss 修改表字段类型 —— 视图依赖批量处理手册适用场景openGaussPostgreSQL 血统中修改某张表的字段类型时报错ERROR: cannot alter type of a column used by a view or rule必须先删除所有依赖该表的视图、改完表后再重建。本手册提供一套批量处理流程无需逐个排查视图。1. 为什么会这样原理openGaussPG 血统MySQL视图存的是什么解析后的查询树pg_rewrite 中按列的内部 OID 绑定视图 SQL文本依赖管理pg_depend 系统表记录列级依赖DDL 时强制检查不做依赖检查改基表字段类型视图列类型是建视图时定死的改了会导致视图语义悄悄变化 →提前报错拦住执行视图时才按名字重新解析类型自动跟着变简单说openGauss 是宁可提前拦住你也不让视图悄悄坏掉MySQL 是先让你改坏了运行时再说MySQL 里把字段删了视图同样会挂只是报错时机晚。这是设计取舍不是缺陷。2. 整体流程第 1 步 秒级预检数一下有多少视图依赖这张表 第 2 步 查出所有依赖视图定义备份到一张表 第 3 步 删视图 → 改表 → 按序重建一个 DO 块失败整体回滚 第 4 步 验证 补授权以下示例把表t_organ的字段org_name改为varchar(100)。执行前替换成自己的表名 / 字段名 / 目标类型。性能注意重要递归查询里不要在递归项中携带 depth 列UNION去重的是整行带 depth 会导致同一视图以不同层级反复传播视图间路径一多行数成倍膨胀且每轮 UNION 都要对累计结果排序去重核心表如组织机构表被全库视图引用会慢到不可用。正确做法递归只收集去重后的 viewoid重建顺序改用view_oidOID 随创建递增依赖者一定晚于被依赖者创建DO 块内多轮重试兜底。3. 第 1 步秒级预检WITHRECURSIVE dep(viewoid)AS(SELECTr.ev_classFROMpg_depend dJOINpg_rewrite rONr.oidd.objidJOINpg_class tONt.oidd.refobjidWHEREd.classidpg_rewrite::regclassANDd.refclassidpg_class::regclassANDt.relnamet_organ-- ★ 表名UNIONSELECTr.ev_classFROMdepJOINpg_depend dONd.refobjiddep.viewoidANDd.classidpg_rewrite::regclassANDd.refclassidpg_class::regclassJOINpg_rewrite rONr.oidd.objid)SELECTcount(*)AS依赖视图总数FROMdep;正常应几秒内出结果几十上百个视图都正常核心表会更多。如果连这个都慢用EXPLAIN ANALYZE分析后另查原因库对象过多等。4. 第 2 步备份依赖视图定义CREATETABLEview_bak_20260910AS-- ★ 备份表名带日期WITHRECURSIVE dep(viewoid)AS(SELECTr.ev_classFROMpg_depend dJOINpg_rewrite rONr.oidd.objidJOINpg_class tONt.oidd.refobjidWHEREd.classidpg_rewrite::regclassANDd.refclassidpg_class::regclassANDt.relnamet_organ-- ★ 表名UNIONSELECTr.ev_classFROMdepJOINpg_depend dONd.refobjiddep.viewoidANDd.classidpg_rewrite::regclassANDd.refclassidpg_class::regclassJOINpg_rewrite rONr.oidd.objid)SELECTn.nspnameASview_schema,v.relnameASview_name,v.oidASview_oid,-- 创建顺序重建时按它排pg_get_viewdef(v.oid,true)ASview_def-- ★ 完整视图定义自动备份FROMdepJOINpg_class vONv.oiddep.viewoidANDv.relkindvJOINpg_namespace nONn.oidv.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 NOTICEDROP %,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 NOTICEALTER OK;-- 3. 按创建顺序view_oid重建个别因顺序问题建失败的下一轮自动重试LOOPv_round :v_round1;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_pending1;-- 依赖还没建好下轮再试END;ENDLOOP;EXITWHENv_pending0ORv_round20;ENDLOOP;IFv_pending0THENRAISE EXCEPTION仍有 % 个视图重建失败请检查备份表中的定义,v_pending;ENDIF;RAISE NOTICE════════ 全部完成共 % 轮════════,v_round;END$$;执行时看 NOTICE 输出最后看到全部完成才算成功中途报错直接整体回滚视图全部自动回来什么都不用补救。这是 openGaussPG 血统比 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. 其他注意事项物化视图备份查询只查了relkind v普通视图。如果库里还有依赖目标表的物化视图relkind m同样会被 ALTER 拦下处理方式相同把过滤条件改为m保存定义用同样的pg_get_viewdef重建后记得REFRESH MATERIALIZED VIEW。函数/存储过程PL/pgSQL 函数体以文本存储不参与依赖检查不受影响但若函数中SELECT了类型变更的列并在体内做了强类型操作需自行回归测试。确认无误后清理操作完成并稳定运行一段时间后可删除备份表DROP TABLE view_bak_20260910;建议至少保留一个迭代周期。备选导出方式gs_dump -d 库名 -s -f schema.sql导出全库结构其中的 CREATE VIEW天然按依赖顺序排好可从中截取需要的部分执行Data Studio / DBeaver 里右键视图导出 DDL 亦可。AOracle兼容模式若DO $$语法报错把脚本改成CREATE OR REPLACE PROCEDURE ...; CALL ...即可逻辑不变。8. 速查卡片Copy-Paste 版-- ① 预检秒级只数个数WITHRECURSIVE dep(viewoid)AS(SELECTr.ev_classFROMpg_depend dJOINpg_rewrite rONr.oidd.objidJOINpg_class tONt.oidd.refobjidWHEREd.classidpg_rewrite::regclassANDd.refclassidpg_class::regclassANDt.relnamet_organUNIONSELECTr.ev_classFROMdepJOINpg_depend dONd.refobjiddep.viewoidANDd.classidpg_rewrite::regclassANDd.refclassidpg_class::regclassJOINpg_rewrite rONr.oidd.objid)SELECTcount(*)FROMdep;-- ② 备份改表名CREATETABLEview_bak_YYYYMMDDASWITHRECURSIVE dep(viewoid)AS(SELECTr.ev_classFROMpg_depend dJOINpg_rewrite rONr.oidd.objidJOINpg_class tONt.oidd.refobjidWHEREd.classidpg_rewrite::regclassANDd.refclassidpg_class::regclassANDt.relnamet_organUNIONSELECTr.ev_classFROMdepJOINpg_depend dONd.refobjiddep.viewoidANDd.classidpg_rewrite::regclassANDd.refclassidpg_class::regclassJOINpg_rewrite rONr.oidd.objid)SELECTn.nspnameASview_schema,v.relnameASview_name,v.oidASview_oid,pg_get_viewdef(v.oid,true)ASview_defFROMdepJOINpg_class vONv.oiddep.viewoidANDv.relkindvJOINpg_namespace nONn.oidv.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_round1;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_pending1;END;ENDLOOP;EXITWHENv_pending0ORv_round20;ENDLOOP;IFv_pending0THENRAISE EXCEPTION仍有 % 个视图重建失败,v_pending;ENDIF;RAISE NOTICE全部完成;END$$;-- ④ 验证 / 补授权 / 稳定后 DROP TABLE view_bak_YYYYMMDD9 直接提供创建函数-- -- 修改被视图引用的表字段类型 - 公共函数(自动删除并重建依赖视图)-- 变更集: 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. 物化视图(relkindm)不自动重建(重建即丢数据), 检测到即抛错,-- 请人工处理-- 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_kindchar,view_deftext)LANGUAGEsqlSTABLESETsearch_pathpg_catalog, pg_tempAS$$WITHRECURSIVE depAS(SELECTr.ev_classASdep_oidFROMpg_depend dJOINpg_rewrite rONr.oidd.objidWHEREd.classidpg_rewrite::regclassANDd.refclassidpg_class::regclassANDd.refobjid(SELECTc.oidFROMpg_class cJOINpg_namespace nONn.oidc.relnamespaceWHEREn.nspnamep_schemaANDc.relnamep_tableLIMIT1)UNIONSELECTr.ev_classFROMdepJOINpg_depend dONd.refobjiddep.dep_oidANDd.classidpg_rewrite::regclassANDd.refclassidpg_class::regclassJOINpg_rewrite rONr.oidd.objid)SELECTv.oidASview_oid,n.nspnameASview_schema,v.relnameASview_name,v.relkindASview_kind,pg_get_viewdef(v.oid,true)ASview_defFROMdepJOINpg_class vONv.oiddep.dep_oidANDv.relkindIN(v,m)JOINpg_namespace nONn.oidv.relnamespaceWHEREn.nspnameNOTLIKEpg\_%ANDn.nspnameinformation_schema$$;-- 修改列类型并自动重建依赖视图: 返回成功重建的视图数量(幂等重跑返回 0)CREATEORREPLACEFUNCTIONpublic.fn_alter_column_type(p_schema name,p_table name,p_column name,p_newtypetext,p_usingtextDEFAULTNULL)RETURNSintLANGUAGEplpgsqlSETsearch_pathpg_catalog, pg_tempAS$$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.oidc.relnamespaceWHEREn.nspnamep_schemaANDc.relnamep_table;IFv_table_oidISNULLTHENRAISE EXCEPTION表 %.% 不存在,p_schema,p_table;ENDIF;SELECTmax(format_type(a.atttypid,a.atttypmod))INTOv_oldtypeFROMpg_attribute aWHEREa.attrelidv_table_oidANDa.attnamep_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.attnamecol_probe;DROPTABLE_type_probe;IFv_oldtypev_newtypeTHENRETURN0;ENDIF;-- 3. 物化视图依赖: 不自动重建(重建即丢数据), 交人工处理SELECTstring_agg(view_schema||.||view_name,, )INTOv_matviewsFROMpublic.fn_list_view_deps(p_schema,p_table)WHEREview_kindm;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_usingTHENv_sql :v_sql|| USING ||p_using;ENDIF;EXECUTEv_sql;-- 7. 循环重建: 重建失败的(其依赖的视图尚未建回)留到下一轮, 最多 20 轮v_round :0;LOOPv_round :v_round1;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_oidr.view_oid;v_done :v_done1;EXCEPTIONWHENundefined_tableORundefined_columnORundefined_functionTHENv_pending :v_pending1;END;ENDLOOP;EXITWHENv_pending0ORv_round20;ENDLOOP;DROPTABLE_dep_views;IFv_pending0THENRAISE EXCEPTION表 %.%.% 类型已改为 %, 但仍有 % 个视图重建失败(超过重试上限), 请人工检查,p_schema,p_table,p_column,p_newtype,v_pending;ENDIF;RETURNv_done;END$$;