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 = "" 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 = "" 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 = "" 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 = "" 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
FROM foo1 f CROSS JOIN foo2 f2;
CREATE VIEW foo4 AS
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@xxxxxxxxxxxxxx> 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
