Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
PostgreSQL: How to handle table and view dependencies
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;
@claudep
Copy link

claudep commented Mar 30, 2015

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

Loading

Copy link

ghost commented Apr 11, 2015

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

Loading

@diogob
Copy link

diogob commented Oct 13, 2015

Beautiful! Saved me a lot of time ❤️

Loading

@maerco
Copy link

maerco commented May 25, 2016

this is not performing backup of rules

Loading

@fosils
Copy link

fosils commented Jun 3, 2016

@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?

Loading

@fosils
Copy link

fosils commented Jun 3, 2016

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');

Loading

@fosils
Copy link

fosils commented Aug 14, 2016

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?

Loading

@shripatel
Copy link

shripatel commented Mar 2, 2017

amazing work , thanks alot.

Loading

@bharathregal
Copy link

bharathregal commented Jun 28, 2018

very helpfull. Thanks again for putting this up.

Loading

@christiaanwesterbeek
Copy link

christiaanwesterbeek commented Aug 27, 2018

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

Loading

@vitalca
Copy link

vitalca commented Oct 9, 2018

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.

Loading

@sergiosbx
Copy link

sergiosbx commented Nov 8, 2018

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;

Loading

@realyota
Copy link

realyota commented Nov 28, 2018

It is MIT licensed?

Loading

@tiitremmel
Copy link

tiitremmel commented Dec 10, 2018

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;

Loading

@Dragonwarrior47
Copy link

Dragonwarrior47 commented Jun 1, 2020

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.

Loading

@briandignan
Copy link

briandignan commented Sep 27, 2020

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

Loading

@rvkulikov
Copy link

rvkulikov commented Dec 31, 2020

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

Loading

@claudep
Copy link

claudep commented Dec 31, 2020

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

Loading

@megamit
Copy link

megamit commented Apr 14, 2021

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

Loading

@aakashrshah
Copy link

aakashrshah commented May 11, 2021

Wouldnt allow if drop done by non-owner.

Loading

@deepanshudj91
Copy link

deepanshudj91 commented Aug 21, 2021

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

Loading

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment