How to list all dependent objects of a table

Akash Shah
Akash Shah
  • Updated

Understanding the complex web of dependencies between database objects is crucial for maintaining data integrity and making informed decisions about database design and maintenance. In this article we will explore how to list all dependent objects of a table. We will create a comprehensive test case that involves the creating of a view which relies on the pg_depend catalog to extract dependency information. With this, we will construct a series of procedures, providing us many tools to analyze the dependency tree of database objects.

Creating the view

We have created the below view, which is used to retrieve information about dependencies between database objects under the report schema. Kindly execute create schema report; before executing the below create view statement.

DROP VIEW IF EXISTS report.dependency;
CREATE OR REPLACE VIEW report.dependency AS
WITH RECURSIVE preference AS (
  SELECT 10 AS max_depth
    , 16384 AS min_oid -- user objects only
    , '^(londiste|pgq|pg_toast)'::text AS schema_exclusion
    , '^pg_(conversion|language|ts_(dict|template))'::text AS class_exclusion
    , '{"SCHEMA":"00", "TABLE":"01", "TABLE CONSTRAINT":"02", "DEFAULT VALUE":"03",
        "INDEX":"05", "SEQUENCE":"06", "TRIGGER":"07", "FUNCTION":"08",
        "VIEW":"10", "MATERIALIZED VIEW":"11", "FOREIGN TABLE":"12"}'::json AS type_sort_orders
)
, dependency_pair AS (
    SELECT objid
      , array_agg(objsubid ORDER BY objsubid) AS objsubids
      , upper(obj.type) AS object_type
      , coalesce(obj.schema, substring(obj.identity, E'(\\w+?)\\.'), '') AS object_schema
      , obj.name AS object_name
      , obj.identity AS object_identity
      , refobjid
      , array_agg(refobjsubid ORDER BY refobjsubid) AS refobjsubids
      , upper(refobj.type) AS refobj_type
      , coalesce(CASE WHEN refobj.type='schema' THEN refobj.identity
                                                ELSE refobj.schema END
          , substring(refobj.identity, E'(\\w+?)\\.'), '') AS refobj_schema
      , refobj.name AS refobj_name
      , refobj.identity AS refobj_identity
      , CASE deptype
            WHEN 'n' THEN 'normal'
            WHEN 'a' THEN 'automatic'
            WHEN 'i' THEN 'internal'
            WHEN 'e' THEN 'extension'
            WHEN 'p' THEN 'pinned'
        END AS dependency_type
    FROM pg_depend dep
      , LATERAL pg_identify_object(classid, objid, 0) AS obj
      , LATERAL pg_identify_object(refclassid, refobjid, 0) AS refobj
      , preference
    WHERE deptype = ANY('{n,a}')
    AND objid >= preference.min_oid
    AND (refobjid >= preference.min_oid OR refobjid = 2200) -- need public schema as root node
    AND coalesce(obj.schema, substring(obj.identity, E'(\\w+?)\\.'), '') !~ preference.schema_exclusion
    AND coalesce(CASE WHEN refobj.type='schema' THEN refobj.identity
                                                ELSE refobj.schema END
          , substring(refobj.identity, E'(\\w+?)\\.'), '') !~ preference.schema_exclusion
    GROUP BY objid, obj.type, obj.schema, obj.name, obj.identity
      , refobjid, refobj.type, refobj.schema, refobj.name, refobj.identity, deptype
)
, dependency_hierarchy AS (
    SELECT DISTINCT
        0 AS level,
        refobjid AS objid,
        refobj_type AS object_type,
        refobj_identity AS object_identity,
        --refobjsubids AS objsubids,
        NULL::text AS dependency_type,
        ARRAY[refobjid] AS dependency_chain,
        ARRAY[concat(preference.type_sort_orders->>refobj_type,refobj_type,':',refobj_identity)] AS dependency_sort_chain
    FROM dependency_pair root
    , preference
    WHERE NOT EXISTS
       (SELECT 'x' FROM dependency_pair branch WHERE branch.objid = root.refobjid)
    AND refobj_schema !~ preference.schema_exclusion
    UNION ALL
    SELECT
        level + 1 AS level,
        child.objid,
        child.object_type,
        child.object_identity,
        --child.objsubids,
        child.dependency_type,
        parent.dependency_chain || child.objid,
        parent.dependency_sort_chain || concat(preference.type_sort_orders->>child.object_type,child.object_type,':',child.object_identity)
    FROM dependency_pair child
    JOIN dependency_hierarchy parent ON (parent.objid = child.refobjid)
    , preference
    WHERE level < preference.max_depth
    AND child.object_schema !~ preference.schema_exclusion
    AND child.refobj_schema !~ preference.schema_exclusion
    AND NOT (child.objid = ANY(parent.dependency_chain)) -- prevent circular referencing
)
SELECT * FROM dependency_hierarchy
ORDER BY dependency_chain ;

The code provided above creates a view in PostgreSQL called report.dependency to unravel the dependencies between the various database objects. This view utilizes a CTE (Common Table Expression) and a recursive query to analyze and extract valuable information about object dependencies, ranging from tables and views to functions and more.

The view, report.dependency, is structured to accommodate various criteria, such as filtering out specific object types and schema. It categorizes dependencies based on their type, whether they are 'normal,' 'automatic,' or 'internal,' among others. The code employs recursive techniques to traverse the dependency tree, establishing relationships between objects and organizing them into a coherent hierarchy.

This view's creation is an essential building block for the comprehensive exploration of PostgreSQL's object dependencies, and it sets the stage for the subsequent development of procedures and functions designed to query and visualize these dependencies, as we'll continue to explore in this article.

Creating procedures to retrieve information with required criteria

-- Procedure to report dependency tree using regexp search pattern (relation-only)

CREATE OR REPLACE FUNCTION report.dependency_tree(search_pattern text)
  RETURNS TABLE(dependency_tree text)
  SECURITY DEFINER LANGUAGE SQL
  AS $function$
WITH target AS (
  SELECT objid, dependency_chain
  FROM report.dependency
  WHERE object_identity ~ search_pattern
)
, list AS (
  SELECT
    format('%*s%s %s', -4*level
          , CASE WHEN object_identity ~ search_pattern THEN '*' END
          , object_type, object_identity
    ) AS dependency_tree
  , dependency_sort_chain
  FROM target
  JOIN report.dependency report
    ON report.objid = ANY(target.dependency_chain) -- root-bound chain
    OR target.objid = ANY(report.dependency_chain) -- leaf-bound chain
  WHERE length(search_pattern) > 0
  -- Do NOT waste search time on blank/null search_pattern.
  UNION
  -- Query the entire dependencies instead.
  SELECT
    format('%*s%s %s', 4*level, '', object_type, object_identity) AS depedency_tree
  , dependency_sort_chain
  FROM report.dependency
  WHERE length(coalesce(search_pattern,'')) = 0
)
SELECT dependency_tree FROM list
ORDER BY dependency_sort_chain;
$function$ ;


-- Procedure to report depedency tree by specific relation name(s) (in text array)

CREATE OR REPLACE FUNCTION report.dependency_tree(object_names text[])
  RETURNS TABLE(dependency_tree text)
  SECURITY DEFINER LANGUAGE SQL
  AS $function$
WITH target AS (
  SELECT objid, dependency_chain
  FROM report.dependency
  JOIN unnest(object_names) AS target(objname) ON objid = objname::regclass
)
, list AS (
  SELECT DISTINCT
    format('%*s%s %s', -4*level
          , CASE WHEN object_identity = ANY(object_names) THEN '*' END
          , object_type, object_identity
    ) AS dependency_tree
  , dependency_sort_chain
  FROM target
  JOIN report.dependency report
    ON report.objid = ANY(target.dependency_chain) -- root-bound chain
    OR target.objid = ANY(report.dependency_chain) -- leaf-bound chain
)
SELECT dependency_tree FROM list
ORDER BY dependency_sort_chain;
$function$ ;


-- Procedure to report dependency tree by oid

CREATE OR REPLACE FUNCTION report.dependency_tree(object_ids oid[])
  RETURNS TABLE(dependency_tree text)
  SECURITY DEFINER LANGUAGE SQL
  AS $function$
WITH target AS (
  SELECT objid, dependency_chain
  FROM report.dependency
  JOIN unnest(object_ids) AS target(objid) USING (objid)
)
, list AS (
  SELECT DISTINCT
    format('%*s%s %s', -4*level
          , CASE WHEN report.objid = ANY(object_ids) THEN '*' END
          , object_type, object_identity
    ) AS dependency_tree
  , dependency_sort_chain
  FROM target
  JOIN report.dependency report
    ON report.objid = ANY(target.dependency_chain) -- root-bound chain
    OR target.objid = ANY(report.dependency_chain) -- leaf-bound chain
)
SELECT dependency_tree FROM list
ORDER BY dependency_sort_chain;
$function$ ;

The above-provided code introduces three essential procedures to report and visualize database object dependencies in PostgreSQL:

  • Procedure for Regexp Search Pattern: This procedure, report.dependency_tree, allows users to search for and report the dependency tree of database objects matching a specified regular expression pattern. It highlights matching objects with an asterisk (*) for easy identification.

  • Procedure for Specific Object Names: The report.dependency_tree procedure accepts an array of object names and provides a hierarchical representation of their dependencies. Matching objects are marked with an asterisk (*).

  • Procedure by Object ID (OID): The third procedure, report.dependency_tree, accepts an array of OIDs and constructs a dependency tree based on the provided object IDs. Objects with matching OIDs are highlighted.

These procedures enhance the functionality of the report.dependency view, enabling users to explore and understand object dependencies using various criteria, such as regular expressions, object names, or OIDs. This aids in tasks like troubleshooting and database optimization.

Creating sample data

We created sample objects to test the above functions by creating tables, views, materialized views, functions, triggers, and constraints. Below is the sample code for the same:

-- Step 1: Create a sample test table

CREATE TABLE test_table (
    id serial PRIMARY KEY,
    name VARCHAR(255),
    age INT,
    email VARCHAR(255)
);

-- Step 2: Create a view based on the test table

CREATE VIEW test_table_view AS
SELECT id, name
FROM test_table;

-- Step 3: Create a materialized view based on the test table

CREATE MATERIALIZED VIEW test_table_materialized_view AS
SELECT id, age
FROM test_table;

-- Step 4: Create a trigger on the test table

CREATE OR REPLACE FUNCTION test_table_trigger_function()
RETURNS TRIGGER AS $$
BEGIN
    -- Your trigger logic here
    -- For example, you can log changes to the table
    INSERT INTO audit_log (table_name, action, timestamp)
    VALUES ('test_table', TG_OP, NOW());
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER test_table_trigger
AFTER INSERT OR UPDATE OR DELETE
ON test_table
FOR EACH ROW
EXECUTE FUNCTION test_table_trigger_function();

-- Step 5: Create a foreign key constraint

CREATE TABLE related_table (
    related_id serial PRIMARY KEY,
    related_name VARCHAR(255),
    test_table_id INT REFERENCES test_table(id)
);

-- Step 6: Create an index on the foreign key column for performance

CREATE INDEX idx_test_table_id ON related_table(test_table_id);

Usage Examples

You have various usage examples for the created function of report.dependency_tree :

-- Examine the entire object hierarchy
SELECT report.dependency_tree('');

-- Dependencies for any relations with names containing match (in regular expression)
SELECT report.dependency_tree('match');

-- Dependencies for relations person & address
SELECT report.dependency_tree('{person,address}'::text[]);

-- Dependencies for function slice
SELECT report.dependency_tree(ARRAY['slice'::regproc]);

-- Dependencies for type hstore
SELECT report.dependency_tree(ARRAY['hstore'::regtype]);

-- Dependencies for triggers by the name updated
SELECT report.dependency_tree(ARRAY(
  SELECT oid FROM pg_trigger WHERE tgname ~ 'updated'
  ));

-- Dependencies for foreign key constraint names starting with product
SELECT report.dependency_tree(ARRAY(
  SELECT oid FROM pg_constraint
  WHERE conname ~ '^product.*_fk'
  ));

Testing

Example:

Checking dependencies for test_table:

edb=# SELECT report.dependency_tree('test_table');
                                                                   dependency_tree                                                                   
-----------------------------------------------------------------------------------------------------------------------------------------------------
 SCHEMA public
     TABLE public.related_table
 *       TABLE CONSTRAINT related_table_test_table_id_fkey on public.related_table
 *       INDEX public.idx_test_table_id
 *   TABLE public.test_table
 *       TABLE CONSTRAINT related_table_test_table_id_fkey on public.related_table
 *       TABLE CONSTRAINT test_table_pkey on public.test_table
 *       DEFAULT VALUE for public.test_table.id
 *       SEQUENCE public.test_table_id_seq
 *           DEFAULT VALUE for public.test_table.id
 *       TRIGGER test_table_trigger on public.test_table
 *       RULE "_RETURN" on public.test_table_materialized_view
 *       RULE "_RETURN" on public.test_table_view
 *   SEQUENCE public.test_table_id_seq
 *       DEFAULT VALUE for public.test_table.id
 *   FUNCTION public.test_table_trigger_function()
 *       TRIGGER test_table_trigger on public.test_table
         PROCEDURE dbms_job.submit(integer,character varying,timestamp without time zone,character varying,boolean,integer,boolean)
         PROCEDURE dbms_job._submit(integer,character varying,timestamp without time zone,character varying,boolean,integer,boolean,pg_catalog.text)
 *   VIEW public.test_table_view
 *       RULE "_RETURN" on public.test_table_view
 *   MATERIALIZED VIEW public.test_table_materialized_view
 *       RULE "_RETURN" on public.test_table_materialized_view
 *INDEX public.test_table_pkey
 *   TABLE CONSTRAINT related_table_test_table_id_fkey on public.related_table
(25 rows)

Checking dependencies for related_table:

edb=# SELECT report.dependency_tree('related_table');
                                                                   dependency_tree                                                                   
-----------------------------------------------------------------------------------------------------------------------------------------------------
 SCHEMA public
 *   TABLE public.related_table
 *       TABLE CONSTRAINT related_table_pkey on public.related_table
 *       TABLE CONSTRAINT related_table_test_table_id_fkey on public.related_table
 *       DEFAULT VALUE for public.related_table.related_id
         INDEX public.idx_test_table_id
 *       SEQUENCE public.related_table_related_id_seq
 *           DEFAULT VALUE for public.related_table.related_id
     TABLE public.test_table
 *       TABLE CONSTRAINT related_table_test_table_id_fkey on public.related_table
 *   SEQUENCE public.related_table_related_id_seq
 *       DEFAULT VALUE for public.related_table.related_id
         PROCEDURE dbms_job._submit(integer,character varying,timestamp without time zone,character varying,boolean,integer,boolean,pg_catalog.text)
 INDEX public.test_table_pkey
 *   TABLE CONSTRAINT related_table_test_table_id_fkey on public.related_table
(15 rows)

Checking dependencies for test_table_trigger:

edb=# SELECT report.dependency_tree(ARRAY(
edb(#   SELECT oid FROM pg_trigger WHERE tgname ~ 'test_table_trigger'
edb(#   ));
                                                          dependency_tree                                                           
------------------------------------------------------------------------------------------------------------------------------------
 SCHEMA public
     TABLE public.test_table
 *       TRIGGER test_table_trigger on public.test_table
     FUNCTION public.test_table_trigger_function()
 *       TRIGGER test_table_trigger on public.test_table
 *       PROCEDURE dbms_job.submit(integer,character varying,timestamp without time zone,character varying,boolean,integer,boolean)
(6 rows)

Checking dependencies for related_table_test_table_id_fkey:

edb=# SELECT report.dependency_tree(ARRAY(
edb(#   SELECT oid FROM pg_constraint
edb(#   WHERE conname ~ 'related_table_test_table_id_fkey'
edb(#   ));
                                                                   dependency_tree                                                                   
-----------------------------------------------------------------------------------------------------------------------------------------------------
 SCHEMA public
     TABLE public.related_table
 *       TABLE CONSTRAINT related_table_test_table_id_fkey on public.related_table
     TABLE public.test_table
 *       TABLE CONSTRAINT related_table_test_table_id_fkey on public.related_table
     FUNCTION public.test_table_trigger_function()
 *       PROCEDURE dbms_job._submit(integer,character varying,timestamp without time zone,character varying,boolean,integer,boolean,pg_catalog.text)
 INDEX public.test_table_pkey
 *   TABLE CONSTRAINT related_table_test_table_id_fkey on public.related_table
(9 rows)

Checking dependencies for related_table_test_table_id_fkey:

edb=# SELECT report.dependency_tree(ARRAY['test_table_trigger_function'::regproc]);
                                                                   dependency_tree                                                                   
-----------------------------------------------------------------------------------------------------------------------------------------------------
 SCHEMA public
 *   FUNCTION public.test_table_trigger_function()
         TRIGGER test_table_trigger on public.test_table
         PROCEDURE dbms_job.submit(integer,character varying,timestamp without time zone,character varying,boolean,integer,boolean)
         PROCEDURE dbms_job._submit(integer,character varying,timestamp without time zone,character varying,boolean,integer,boolean,pg_catalog.text)
(5 rows)

As we can observe from the above examples all the dependent objects are shown when checking for the dependencies of a specific object.

In the realm of database management, understanding and managing object dependencies is crucial for ensuring data integrity, facilitating maintenance, and making informed decisions about database design. In this journey through PostgreSQL, we've explored the intricate web of dependencies between database objects and how to navigate it effectively.

Through the creation of a comprehensive view, report.dependency, and a series of well-crafted procedures, we've empowered ourselves to examine and report object dependencies in a myriad of ways. Whether searching for dependencies based on regular expressions, specific object names, OIDs, or specific types, we've gained the tools needed to visualize the intricate relationships between database objects.

As our usage examples have demonstrated, these functions allow us to uncover not just the direct dependencies but the entire chain of related objects, providing a holistic view of the database landscape. With these tools at our disposal, we can perform tasks ranging from troubleshooting and auditing to optimization and enhancing data security.

As we conclude this exploration, remember that the ability to identify and understand object dependencies is not only a valuable asset but a key to maintaining a well-structured and efficient database. We hope that the knowledge and tools shared in this article will serve you well in your PostgreSQL database management endeavors.

Was this article helpful?

0 out of 0 found this helpful