Pivotal Knowledge Base

Follow

Script - Check the dependencies of the object.

Goal

Is to provide a simple scripts to help administrator identify dependencies of object in the database.

Note : Please verify the script on a test cluster , before running it on production .

Solution

Script to check the objects dependencies at one level.

select
    c1.relname as "View",
    c2.relname as "Dependent Objects"
from
    pg_class c1
    join pg_namespace n on (c1.relnamespace = n.oid)
    join pg_rewrite r on (c1.oid = r.ev_class)
    join pg_depend d on (r.oid = d.objid)
    join pg_class c2 on (c2.oid = d.refobjid)
where
    d.refobjid != c1.oid
    and n.nspname = 'public'
    and c1.relname = 'v2'
order by
    1,2
;

Script to check the object dependencies at multiple level (recursive)

create or replace function view_dep_objects(oid, int) returns setof text
as
$$
declare
   rec record;
   sql text;
   i int;
   prefix text;
   output text;
   sql2 text;
begin
   prefix := '';
   for i in 1..$2 loop prefix := prefix || ' '; end loop;
 
   sql :=
'select
    c2.oid, n.nspname, c2.*
from
    pg_class c1
    join pg_namespace n on (c1.relnamespace = n.oid)
    join pg_rewrite r on (c1.oid = r.ev_class)
    join pg_depend d on (r.oid = d.objid)
    join pg_class c2 on (c2.oid = d.refobjid)
where
    d.refobjid != c1.oid
    and c1.oid =
' || $1;
 
   for rec in execute sql loop
      output := prefix || rec.nspname || '.' || rec.relname;
      return next output;
 
      if (rec.relkind = 'v') then
         sql2 := 'select * from view_dep_objects(' || rec.oid || ', ' || $2 + 3 || ');';
         for output in execute sql2 loop
            output := prefix || output;
            return next output;
         end loop;
      end if;
   end loop;
end;
$$ language plpgsql;

Sample output

  • Objects created
gpadmin=# \d v1
       View "public.v1"
Column |  Type   | Modifiers
--------+---------+-----------
a      | integer |
b      | integer |
View definition:
SELECT t1.a, t2.b
   FROM t1, t2;
 
gpadmin=# \d v2
      View "public.v2"
Column |  Type  | Modifiers
--------+--------+-----------
count  | bigint |
View definition:
SELECT count(*) AS count
   FROM t1, t2, v1;
 
gpadmin=# \d v3
      View "public.v3"
Column |  Type  | Modifiers
--------+--------+-----------
count  | bigint |

View definition:
SELECT v2.count
   FROM v2;
  • The objects dependencies at one level.
gpadmin=# select
    c1.relname as "View",
    c2.relname as "Dependent Objects"
from
    pg_class c1
    join pg_namespace n on (c1.relnamespace = n.oid)
    join pg_rewrite r on (c1.oid = r.ev_class)
    join pg_depend d on (r.oid = d.objid)
    join pg_class c2 on (c2.oid = d.refobjid)
where
    d.refobjid != c1.oid
    and n.nspname = 'public'
    and c1.relname = 'v2'
order by
    1,2
;
 
View | Dependent Objects
------+-------------------
v2   | t1
v2   | t2
v2   | v1
(3 rows)
  • The objects dependencies at multiple level.
create or replace function view_dep_objects(oid, int) returns setof text
as
$$
declare
   rec record;
   sql text;
   i int;
   prefix text;
   output text;
   sql2 text;
begin
   prefix := '';
   for i in 1..$2 loop prefix := prefix || ' '; end loop;
 
   sql :=
'select
    c2.oid, n.nspname, c2.*
from
    pg_class c1
    join pg_namespace n on (c1.relnamespace = n.oid)
    join pg_rewrite r on (c1.oid = r.ev_class)
    join pg_depend d on (r.oid = d.objid)
    join pg_class c2 on (c2.oid = d.refobjid)
where
    d.refobjid != c1.oid
    and c1.oid =
' || $1;
 
   for rec in execute sql loop
      output := prefix || rec.nspname || '.' || rec.relname;
      return next output;
 
      if (rec.relkind = 'v') then
         sql2 := 'select * from view_dep_objects(' || rec.oid || ', ' || $2 + 3 || ');';
         for output in execute sql2 loop
            output := prefix || output;
            return next output;
         end loop;
      end if;
   end loop;
end;
$$ language plpgsql;
 
 
gpadmin=# select * from  view_dep_objects('v3'::regclass,0);
  view_dep_objects
--------------------
public.v2
    public.t1
    public.t2
    public.v1
          public.t1
          public.t2
(6 rows)

Comments

Powered by Zendesk