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;

Awesome script. I've added name quoting (I have views names with spaces) in https://gist.github.com/claudep/b3c2e348eab19896fa1c/revisions

does this function restore MV and indexes? I have a MV base on selecting from another MV. Thx.

Beautiful! Saved me a lot of time ❤️

this is not performing backup of rules

@maerco Thank you for your comment. Do you know how it behaves if there are rules involved. Will they just get dropped or will it fail with an error message?

This script seems to work so far. Brilliant. It solved this problem for me:
https://forums.aws.amazon.com/thread.jspa?threadID=225781&tstart=0

These were the commands I ran to add a label to an enum on Amazon RDS.

CREATE TYPE "public"."enum_new" AS ENUM ('value1', 'value2');
select deps_save_and_drop_dependencies('public', 'customers');
alter table customers alter column some_column type enum_new using enum_type::text::enum_new;
select deps_restore_dependencies('public', 'customers');

I would pay $80 if anyone would add the ability to also store rules on any of the dropped tables/views or is it already like that?

amazing work , thanks alot.

very helpfull. Thanks again for putting this up.

Some of my dependant views have dashes (*) and I'm getting this error after running.

ERROR: syntax error at or near "-"
LINE 1: DROP VIEW api.car-washes

I will implement a fix for myself, but this is just to let you know.

* Why? I'm using PostgREST with a sepatate schema that hosts views and functions exposed via rest (based on permissions). I url's dashes are more commonly usually used in resource names and the view names automatically become part of that

Thanks Mateusz, this is a really nice trick!
There is a suggestion though. I would create functions by DBA role (postgres) with SECURITY DEFINER parameter and then execute 'ALTER ... OWNER TO ...' after restoring views. Because in real life there can be dependencies between objects in different schemas, owned by different roles.

does this function restore MV and indexes? I have a MV base on selecting from another MV. Thx.

To add support for INDEX backups, add this code snippet to line 47:

insert into deps_saved_ddl(deps_view_schema, deps_view_name, deps_ddl_to_run)
select distinct p_view_schema, p_view_name, indexdef
FROM pg_indexes
WHERE schemaname = v_curr.obj_schema
AND tablename = v_curr.obj_name;

It is MIT licensed?

I would pay $80 if anyone would add the ability to also store rules on any of the dropped tables/views or is it already like that?

@christiaanwesterbeek or anyone else still intrested support for RULE backups, add this code snippet to line 47:

insert into deps_saved_ddl(deps_view_schema, deps_view_name, deps_ddl_to_run)
select distinct tablename, rulename, definition
FROM pg_rules
WHERE schemaname = v_curr.obj_schema
AND tablename = v_curr.obj_name;

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)