Re: Dependency tree to tie type/function deps to a table

Here is my current effort. I would love feedback in case I've missed
something. I also know there is perhaps a little redundancy in the
recursion but it's looking quick enough. The query below has the relname
of the base table hardcoded but I would generalize this once happy with it.

WITH RECURSIVE base AS (
SELECT DISTINCT
1 AS level,
classid,
objid,
refclassid,
refobjid,
ev_class,
cv.relname AS view_name
FROM pg_depend d
/****
Get the view oid and name if it's a view
*/
LEFT JOIN pg_rewrite r
ON d.classid = (SELECT oid FROM pg_class WHERE relname = 'pg_rewrite')
AND r.oid = d.objid
LEFT JOIN pg_class cv
ON cv.oid = r.ev_class
/****
This is designed to look for a single object's dependencies for use with
drop/recreate
But could perhaps be tweaked if we want to look for something else or
multiple base objects
*/
WHERE refobjid = (SELECT oid
FROM pg_class
WHERE relname = 'foo1' AND relpersistence = 'p')
--Ignore cases where view oid = refobjid
AND (d.refobjid <> r.ev_class OR r.ev_class IS NULL)
UNION ALL
SELECT DISTINCT
level + 1 AS level,
d.classid,
d.objid,
d.refclassid,
d.refobjid,
r.ev_class,
cv.relname AS view_name
FROM pg_depend d
INNER JOIN base b
/***
If it's a view, get the view oid from pg_rewrite to look for that
dependency
instead of the rule. Otherwise, use classid and objid as-is.
*/
ON CASE
WHEN b.ev_class IS NULL THEN d.refclassid = b.classid
ELSE d.refclassid = (SELECT oid FROM pg_class WHERE relname =
'pg_class')
END
AND
CASE
WHEN b.ev_class IS NULL THEN d.refobjid = b.objid
ELSE d.refobjid = b.ev_class
END
LEFT JOIN pg_rewrite r
ON d.classid = (SELECT oid FROM pg_class WHERE relname = 'pg_rewrite')
AND r.oid = d.objid
LEFT JOIN pg_class cv
ON cv.oid = r.ev_class
WHERE
--prevent infinite recursion - probably should be removed if the query
is right
level < 10
--no identical matches with base
AND NOT (d.classid = b.classid AND d.objid = b.objid AND d.refclassid
= b.refclassid AND d.refobjid = b.refobjid)
--Ignore cases where view oid = refobjid
AND (d.refobjid <> r.ev_class OR r.ev_class IS NULL)
)

/***
Since we know there are at least a few duplicates in classid + objid,
only find unique cases, but find row_number order.
*/
, distinct_objs AS (
SELECT DISTINCT ON (classid, objid)
classid, objid, view_name, ev_class, rn
FROM
(SELECT *,
ROW_NUMBER() OVER() AS rn
FROM base) brn
ORDER BY classid, objid, rn
)

, objects_we_want_to_recreate AS
(
SELECT
/***
Describe/identify view instead of rule if it's a view, otherwise, take
classid and objid as-is
*/
CASE WHEN view_name IS NOT NULL
THEN pg_describe_object((SELECT oid FROM pg_class WHERE relname =
'pg_class'), d.ev_class, 0)
ELSE pg_describe_object(classid, objid, 0)
END AS desc_obj,
CASE WHEN view_name IS NOT NULL
THEN (pg_identify_object((SELECT oid FROM pg_class WHERE relname =
'pg_class'), d.ev_class, 0)).type
ELSE (pg_identify_object(classid, objid, 0)).type
END AS ident_type,
CASE WHEN view_name IS NOT NULL
THEN (pg_identify_object((SELECT oid FROM pg_class WHERE relname =
'pg_class'), d.ev_class, 0)).identity
ELSE (pg_identify_object(classid, objid, 0)).identity
END AS ident_identity,
classid,
objid,
view_name,
rn
FROM distinct_objs d
LEFT JOIN pg_type t
ON d.classid = (SELECT oid FROM pg_class WHERE relname = 'pg_type')
AND t.oid = d.objid
LEFT JOIN pg_class tc
ON tc.oid = t.typrelid
WHERE ((t.typtype <> 'b' --ignore base types
and tc.relkind = 'c' --no need to manually drop and recreate types
tied to other relkinds
)
or t.oid is null)
)

SELECT * FROM objects_we_want_to_recreate ORDER BY rn DESC;

Here is a little example:

CREATE TABLE foo1 (id int);
CREATE TABLE foo2 (id int);
CREATE VIEW foo3 AS
SELECT f.id, f2.id AS id2
FROM foo1 f CROSS JOIN foo2 f2;
CREATE VIEW foo4 AS
SELECT f.id, f2.id AS id2
FROM foo1 f CROSS JOIN foo3 f2;
CREATE VIEW foo5 AS
SELECT * FROM foo4;
CREATE FUNCTION foo() RETURNS SETOF foo1 AS 'SELECT * FROM foo1;' LANGUAGE
SQL;
CREATE FUNCTION foo6() RETURNS SETOF foo5 AS 'SELECT * FROM foo5;' LANGUAGE
SQL;
CREATE MATERIALIZED VIEW foo8 AS
SELECT * FROM foo1;
CREATE TYPE foo9 AS (foo foo1, bar text);

And query results:
*desc_obj* *ident_type* *ident_identity* *classid* *objid* *view_name* *rn*
function foo6() function public.foo6() 1255 24182 19
composite type foo9 composite type public.foo9 1259 24187 11
view foo5 view public.foo5 2618 24180 foo5 8
function foo() function public.foo() 1255 24181 6
materialized view foo8 materialized view public.foo8 2618 24186 foo8 4
view foo4 view public.foo4 2618 24176 foo4 3
view foo3 view public.foo3 2618 24172 foo3 2

If I drop these in order of appearance, it all works and finally lets me
drop table foo1 without cascade.

Thanks,
Jeremy

On Wed, Dec 13, 2017 at 1:31 PM, Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>
wrote:

> Jeremy Finzel wrote:
>
> > I appreciate that, Melvin. However, this doesn't do the recursive part.
> > It doesn't show me type or function dependencies, for example:
>
> You need a WITH RECURSIVE query ...
>
> If you do figure it out, please publish it as in this section of the wiki
> https://wiki.postgresql.org/wiki/Category:Snippets
>
> (In general, it would be good to have lots of contents in Snippets, so
> feel free to add stuff that you think may be of general usefulness.)
>
> --
> Álvaro Herrera https://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>