PostgreSQL: How to handle table and view dependencies

save_restore_dependencies.sql

create table deps_saved_ddl
(
  deps_id serial primary key, 
  deps_view_schema varchar(255), 
  deps_view_name varchar(255), 
  deps_ddl_to_run text
);

create or replace function deps_save_and_drop_dependencies(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_view_schema, deps_view_name, deps_ddl_to_run)
  select 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_view_schema, deps_view_name, deps_ddl_to_run)
  select 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_view_schema, deps_view_name, deps_ddl_to_run)
  select 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_view_schema, deps_view_name, deps_ddl_to_run)
    select 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_view_schema, deps_view_name, deps_ddl_to_run)
    select 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;

create or replace function deps_restore_dependencies(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
  order by deps_id desc
) loop
  execute v_curr.deps_ddl_to_run;
end loop;
delete from deps_saved_ddl
where deps_view_schema = p_view_schema and deps_view_name = p_view_name;
end;
$$
LANGUAGE plpgsql;

Does it restore the roles of a dependent materialized view? I have a materialized view and another dependent materialize view. When I run your script on base materialized view it does not restore all the permissions assigned to the dependent materialized view.

This script is incredible! I changed some types to get it working on PG12 because of this error:

mydb=# select deps_save_and_drop_dependencies('public', 'test_table');
ERROR:  recursive query "recursive_deps" column 1 has collation "default" in non-recursive term but collation "C" overall
LINE 6:     select p_view_schema, p_view_name, null::varchar, 0

Also included are commits by @claudep and index/rule suggestions by @sergiosbx and @tiitremmel

https://gist.github.com/briandignan/03ef42e78434658cf27f052e2f0798e8

Added support of owners, material views, column grants etc.
https://github.com/rvkulikov/pg-deps-management

@rvkulikov, great idea to turn this gist into a real repo! Adding a bit more comments might be a welcome addition.

Small issue with this is calling it on a materialzed view will not save its indexes

Wouldnt allow if drop done by non-owner.

Hey guys.. does it work with postgre 12? I am getting error.

SQL Error [42P21]: ERROR: recursive query "recursive_deps" column 1 has collation "default" in non-recursive term but collation "C" overall
Hint: Use the COLLATE clause to set the collation of the non-recursive term.
Where: PL/pgSQL function deps_save_and_drop_dependencies(character varying,character varying) line 5 at FOR over SELECT rows

Please help

Hey guys.. does it work with postgre 12? I am getting error.

SQL Error [42P21]: ERROR: recursive query "recursive_deps" column 1 has collation "default" in non-recursive term but collation "C" overall Hint: Use the COLLATE clause to set the collation of the non-recursive term. Where: PL/pgSQL function deps_save_and_drop_dependencies(character varying,character varying) line 5 at FOR over SELECT rows

Please help

To fix this problem modify lines 20 to 22 as such. Just need to collate those columns to "C"

select p_view_schema collate "C", p_view_name collate "C", null::varchar collate "C", 0
union
select dep_schema::varchar collate "C", dep_name::varchar collate "C", dep_type::varchar collate "C", recursive_deps.depth + 1 from

Thanks for this gist. It's really helpful + the comments.

I noticed that it was skipping the recreation of materialized view permissions which are not stored in the information schema and added the below to capture them during object recreation

-- Save materialized views permissions. Why? Their information will be missing in the information schema since they are not SQL standard SQL objects
insert into public.deps_saved_ddl(deps_view_schema, deps_view_name, deps_ddl_to_run)
select p_view_schema, p_view_name, 'GRANT ' || priviliges_from_acl(s[2]) || ' ON ' || n.nspname || '.' || c.relname || ' TO ' || coalesce(nullif(s[1], ''), 'public')
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
join pg_roles r on r.oid = relowner,
unnest(coalesce(relacl::text[], format('{%s=arwdDxt/%s}', rolname, rolname)::text[])) acl,
regexp_split_to_array(acl, '=|/') s
where n.nspname = v_curr.obj_schema and c.relname = v_curr.obj_name;

Thanks a lot, this has been bothering me for quite a while until now :)
This is the most intuitive version to adress the underlying problem amongst various other approaches (like manually just dropping all views and restoring them from a bkp or sql file)