解决依赖问题

• 1.问题的背景
• 2.解决方案思路
• • 2.1、备份依赖项项
  • • 2.1.1，创建备份表
    • 2.1.2创建备份所依赖的存储过程。
  • 2.2，修改字段类型
  • 2.3，恢复依赖
• 3.日常通话

1.问题的背景

PG数据库：当您需要修改表的字段类型时，突然报告错误(-_-）

从错误消息中可以看出，该表的此字段被某个视图使用。

2.解决方案思路

解决方案：

• 备份依赖

• 修改字段类型

• 恢复依赖

• • *

2.1、备份依赖项项

2.1.1，创建备份表

--在使用之前，先初始化相应的方法

CREATE TABLE  deps_saved_ddl (
    deps_id serial NOT NULL, -- 依赖ID
    deps_view_schema varchar(255) NULL, -- SCHEMA
    deps_view_name varchar(255) NULL, -- 依赖表名称
    deps_ddl_to_run text NULL, -- 依赖的DDL
    deps_version varchar(255) NULL, -- 版本
    is_deleted bpchar(1) NULL DEFAULT 0, -- 我能把它删除吗
    CONSTRAINT deps_saved_ddl_pkey PRIMARY KEY (deps_id)
);
COMMENT ON TABLE  deps_saved_ddl IS 用户修改字段时的备份依赖关系;

-- Column comments

COMMENT ON COLUMN  deps_saved_ddl.deps_id IS 依赖ID;
COMMENT ON COLUMN  deps_saved_ddl.deps_view_schema IS SCHEMA;
COMMENT ON COLUMN  deps_saved_ddl.deps_view_name IS 依赖表名称;
COMMENT ON COLUMN  deps_saved_ddl.deps_ddl_to_run IS 依赖的DDL;
COMMENT ON COLUMN  deps_saved_ddl.deps_version IS 版本;
COMMENT ON COLUMN  deps_saved_ddl.is_deleted IS 我能把它删除吗;
;

2.1.2创建备份所依赖的存储过程。

--创建备份所依赖的存储过程。
create or replace function deps_save_and_drop_dependencies(p_version varchar, p_view_schema varchar, p_view_name varchar) returns void as
$$
declare
  v_curr record;
begin
for v_curr in
(
  select obj_schema, obj_name, obj_type from
  (
  with recursive recursive_deps(obj_schema, obj_name, obj_type, depth) as
  (
    select p_view_schema, p_view_name, null::varchar, 0
    union
    select dep_schema::varchar, dep_name::varchar, dep_type::varchar, recursive_deps.depth + 1 from
    (
      select ref_nsp.nspname ref_schema, ref_cl.relname ref_name,
      rwr_cl.relkind dep_type,
      rwr_nsp.nspname dep_schema,
      rwr_cl.relname dep_name
      from pg_depend dep
      join pg_class ref_cl on dep.refobjid = ref_cl.oid
      join pg_namespace ref_nsp on ref_cl.relnamespace = ref_nsp.oid
      join pg_rewrite rwr on dep.objid = rwr.oid
      join pg_class rwr_cl on rwr.ev_class = rwr_cl.oid
      join pg_namespace rwr_nsp on rwr_cl.relnamespace = rwr_nsp.oid
      where dep.deptype = n
      and dep.classid = pg_rewrite::regclass
    ) deps
    join recursive_deps on deps.ref_schema = recursive_deps.obj_schema and deps.ref_name = recursive_deps.obj_name
    where (deps.ref_schema != deps.dep_schema or deps.ref_name != deps.dep_name)
  )
  select obj_schema, obj_name, obj_type, depth
  from recursive_deps
  where depth > 0
  ) t
  group by obj_schema, obj_name, obj_type
  order by max(depth) desc
) loop

  insert into deps_saved_ddl(deps_version, deps_view_schema, deps_view_name, deps_ddl_to_run)
  select p_version, p_view_schema, p_view_name, COMMENT ON  ||
  case
  when c.relkind = v then VIEW
  when c.relkind = m then MATERIALIZED VIEW
  else 
  end
  ||   || n.nspname || . || c.relname ||  IS  || replace(d.description, , ) || ;
  from pg_class c
  join pg_namespace n on n.oid = c.relnamespace
  join pg_description d on d.objoid = c.oid and d.objsubid = 0
  where n.nspname = v_curr.obj_schema and c.relname = v_curr.obj_name and d.description is not null;

  insert into deps_saved_ddl(deps_version, deps_view_schema, deps_view_name, deps_ddl_to_run)
  select p_version, p_view_schema, p_view_name, COMMENT ON COLUMN  || n.nspname || . || c.relname || . || a.attname ||  IS  || replace(d.description, , ) || ;
  from pg_class c
  join pg_attribute a on c.oid = a.attrelid
  join pg_namespace n on n.oid = c.relnamespace
  join pg_description d on d.objoid = c.oid and d.objsubid = a.attnum
  where n.nspname = v_curr.obj_schema and c.relname = v_curr.obj_name and d.description is not null;

  insert into deps_saved_ddl(deps_version, deps_view_schema, deps_view_name, deps_ddl_to_run)
  select p_version, p_view_schema, p_view_name, GRANT  || privilege_type ||  ON  || table_schema || . || table_name ||  TO  || grantee
  from information_schema.role_table_grants
  where table_schema = v_curr.obj_schema and table_name = v_curr.obj_name;

  if v_curr.obj_type = v then
    insert into deps_saved_ddl(deps_version, deps_view_schema, deps_view_name, deps_ddl_to_run)
    select p_version, p_view_schema, p_view_name, CREATE VIEW  || v_curr.obj_schema || . || v_curr.obj_name ||  AS  || view_definition
    from information_schema.views
    where table_schema = v_curr.obj_schema and table_name = v_curr.obj_name;
  elsif v_curr.obj_type = m then
    insert into deps_saved_ddl(deps_version, deps_view_schema, deps_view_name, deps_ddl_to_run)
    select p_version, p_view_schema, p_view_name, CREATE MATERIALIZED VIEW  || v_curr.obj_schema || . || v_curr.obj_name ||  AS  || definition
    from pg_matviews
    where schemaname = v_curr.obj_schema and matviewname = v_curr.obj_name;
  end if;

  execute DROP  ||
  case
    when v_curr.obj_type = v then VIEW
    when v_curr.obj_type = m then MATERIALIZED VIEW
  end
  ||   || v_curr.obj_schema || . || v_curr.obj_name;

end loop;
end;
$$
LANGUAGE plpgsql;

2.2，修改字段类型

alter table MD_PART_FAMILY alter column PLATEFORM_SCHEME_ID type text using PLATEFORM_SCHEME_ID::text;
alter table MD_PART_FAMILY alter column PLATEFORM_SCHEME type text using PLATEFORM_SCHEME::text;

2.3，恢复依赖

--创建还原所依赖的存储过程。
create or replace function deps_restore_dependencies(p_version varchar, p_view_schema varchar, p_view_name varchar) returns void as
$$
declare
  v_curr record;
begin
for v_curr in
(
  select deps_ddl_to_run
  from deps_saved_ddl
  where deps_view_schema = p_view_schema and deps_view_name = p_view_name
  and is_deleted = 0
  order by deps_id desc
) loop
  execute v_curr.deps_ddl_to_run;
end loop;
update deps_saved_ddl set is_deleted = 1
where deps_view_schema = p_view_schema and deps_view_name = p_view_name and deps_version = p_version;
end;
$$
LANGUAGE plpgsql;

3.日常通话

-- 备份依赖
select deps_save_and_drop_dependencies(2021072200002, md, md_plateform_scheme);
-- 修改字段类型
alter table md_plateform_scheme alter column pre_plateform_scheme type text using pre_plateform_scheme::text;
-- 恢复依赖
select md.deps_restore_dependencies(2021072200002, md, md_plateform_scheme);

1.表名需要小写
2.版本号：日期足够