How to find all object which will be dropped by CASCADE?

Question

When I DROP something like this

ALTER TABLE "order_bt" DROP COLUMN "billed_to"

the command will list dependencies of the billed_to column:

DETAIL:  view "order" depends on column billed_to of table order_bt

But, this view order also has associated triggers, which would also be dropped when adding CASCADE to the above DROP command.

Is there an option that will allow me to see the full list of what would be dropped with DROP ... CASCADE?

I think this [answer](https://stackoverflow.com/a/37977656/15424227) can help you. — nachospiu, Aug 20 '21 at 12:44
@nachospiu: Thank you. But accepted answer does not show that some triggers depend on my view for "order" — Eugen Konkov, Aug 20 '21 at 12:47
You are welcome! Read: "To find the full chain of cascading dependencies we should use recursion...". I think you will need to modify the query, but it will show you the way. You can read [pg_trigger doc](https://www.postgresql.org/docs/current/catalog-pg-trigger.html). — nachospiu, Aug 20 '21 at 13:01
@nachospiu: I run exactly that query (changing 'the_table' by 'order' before), and get no info back. Anyway thank you for links, I will read them. — Eugen Konkov, Aug 20 '21 at 13:05

score 2 · Accepted Answer · answered Aug 23 '21 at 15:20

I created an example structure to check dependencies. You can try it on your database, of course objects ID will be different.

CREATE TABLE parents (
  id_parent integer NOT NULL,
  name_parent varchar(100),
  CONSTRAINT pk_parents_id PRIMARY KEY (id_parent)
);

CREATE TABLE childs (
  id_child integer NOT NULL,
  name_child varchar(100),
  parent_id integer,
  CONSTRAINT pk_childs_id PRIMARY KEY (id_child),
  CONSTRAINT fk_childs_parent_id__parents FOREIGN KEY (parent_id)
      REFERENCES parents (id_parent) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE OR REPLACE VIEW public.parents_view AS 
 SELECT parents.id_parent,
    parents.name_parent
   FROM parents;

CREATE FUNCTION test_trigger() RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' then
        raise notice 'INSERT trigger, NEW = [%]', NEW;
    ELSIF TG_OP = 'UPDATE' then
        raise notice 'UPDATE trigger, OLD = [%], NEW = [%]', OLD, NEW;
    ELSE
        raise notice 'DELETE trigger, OLD = [%]', OLD;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER parents_view_trigger INSTEAD OF INSERT OR UPDATE OR DELETE ON parents_view FOR EACH ROW EXECUTE PROCEDURE test_trigger();

Objects that depend of table "parents":

SELECT 
    refclassid, refobjid, refobjsubid,
    pg_describe_object(refclassid, refobjid, refobjsubid), 
    --pg_identify_object (refclassid, refobjid, refobjsubid) ,

    classid, objid, objsubid,
    pg_describe_object(classid, objid, objsubid), 
    --pg_identify_object (classid, objid, objsubid) ,
    pg_get_constraintdef(objid),

    deptype
    
FROM pg_depend 
WHERE refobjid IN ('parents'::regclass) -- and deptype IN ('n', 'a')
ORDER BY classid, refobjid;

Output:

refclassid	refobjid	refobjsubid	pg_describe_object refobject	classid	objid	pg_describe_object object	pg_get_constraintdef	deptype
1259	2305241	0	table parents	1247	2305243	type parents		i
1259	2305241	1	table parents column id_parent	2606	2305245	constraint pk_parents_id on table parents	PRIMARY KEY (id_parent)	a
1259	2305241	1	table parents column id_parent	2606	2305251	constraint fk_childs_parent_id__parents on table childs	FOREIGN KEY (parent_id) REFERENCES parents(id_parent) ON UPDATE CASCADE ON DELETE CASCADE	n
1259	2305241	1	table parents column id_parent	2618	2305259	rule _RETURN on view parents_view		n
1259	2305241	2	table parents column name_parent	2618	2305259	rule _RETURN on view parents_view		n

Objects that depend of view "parents_view":

SELECT 
    refclassid, refobjid, refobjsubid,
    pg_describe_object(refclassid, refobjid, refobjsubid), 
    --pg_identify_object (refclassid, refobjid, refobjsubid),

    classid, objid, objsubid,
    pg_describe_object(classid, objid, objsubid), 
    --pg_identify_object (classid, objid, objsubid) ,
    pg_get_constraintdef(objid),

    deptype
    
FROM pg_depend 
WHERE refobjid IN ('parents_view'::regclass) -- and deptype IN ('n', 'a')
ORDER BY classid, refobjid;

Output:

refclassid	refobjid	pg_describe_object refobj	classid	objid	pg_describe_object obj	deptype
1259	2305256	view parents_view	1247	2305258	type parents_view	i
1259	2305256	view parents_view	2618	2305259	rule _RETURN on view parents_view	i
1259	2305256	view parents_view	2618	2305259	rule _RETURN on view parents_view	n
1259	2305256	view parents_view	2620	2305260	trigger parents_view_trigger on view parents_view	a

You can check that view "parents_view" (object ID: 2305256) not depend of table parents but rule "rule _RETURN on view parents_view" (object ID: 2305259) does, and "trigger parents_view_trigger on view parents_view" depends on parents_view, this is the reason why recursive query do not return view dependent information.

Triggers are DEPENDENCY_AUTO type (a): The dependent object can be dropped separately from the referenced object, and should be automatically dropped (regardless of RESTRICT or CASCADE mode) if the referenced object is dropped. Example: a named constraint on a table is made auto-dependent on the table, so that it will go away if the table is dropped.

You can try a query like this, but you should try to improve it:

You can restrict the result filtering by deptype.


WITH RECURSIVE pg_depend_recursive AS (
                SELECT d.refclassid, d.refobjid, d.refobjsubid, d.classid, d.objid, d.objsubid, d.deptype
                FROM pg_depend d
                where refobjid = 'parents'::regclass

                UNION

                SELECT d.refclassid, d.refobjid, d.refobjsubid, d.classid, d.objid, d.objsubid, d.deptype
                FROM pg_depend d
                INNER JOIN pg_depend_recursive dr ON d.refobjid = dr.objid),

        pg_depend_recursive2 AS (SELECT d.refclassid, d.refobjid, d.refobjsubid, d.classid, d.objid, d.objsubid, d.deptype 
                    FROM pg_depend_recursive d

                    UNION

                    SELECT d.refclassid, d.refobjid, d.refobjsubid, d.classid, d.objid, d.objsubid, d.deptype
                    FROM pg_depend d
                    INNER JOIN pg_depend_recursive2 dr ON d.objid = dr.objid AND pg_describe_object(dr.classid, dr.objid, dr.objsubid) ilike 'rule%'),
                    
        pg_depend_recursive3 AS (SELECT d.refclassid, d.refobjid, d.refobjsubid, d.classid, d.objid, d.objsubid, d.deptype 
                    FROM pg_depend_recursive2 d

                    UNION

                    SELECT d.refclassid, d.refobjid, d.refobjsubid, d.classid, d.objid, d.objsubid, d.deptype
                    FROM pg_depend d
                    INNER JOIN pg_depend_recursive3 dr ON d.refobjid = dr.refobjid)
                    
SELECT refclassid, refobjid, refobjsubid, pg_describe_object(refclassid, refobjid, refobjsubid),
       classid, objid, objsubid, pg_describe_object(classid, objid, objsubid),
       deptype
FROM pg_depend_recursive3;

Works fine. But to note, it cannot list objects (like RLS policies) dependent on a function. — tanius, Feb 23 '23 at 03:01

How to find all object which will be dropped by CASCADE?

1 Answers1