Dependency tree to tie type/function deps to a table

Jeremy Finzel <finzelj@gmail.com> writes:

It looks like the very useful dependency tree shown when using DROP CASCADE
is written in C in dependency.c, but there is no way to leverage this
within Postgres to actually query an object's dependencies. Can we get
this somehow as a Postgres client in SQL?

Seems like you could build a query for that easily enough using a
recursive union over pg_depend plus pg_describe_object() to produce
text descriptions of the entries.

regards, tom lane

On Wed, Dec 13, 2017 at 10:20 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Jeremy Finzel <finzelj@gmail.com> writes:

It looks like the very useful dependency tree shown when using DROP

CASCADE

is written in C in dependency.c, but there is no way to leverage this
within Postgres to actually query an object's dependencies. Can we get
this somehow as a Postgres client in SQL?

Seems like you could build a query for that easily enough using a
recursive union over pg_depend plus pg_describe_object() to produce
text descriptions of the entries.

regards, tom lane

Jeremy ,

per Tom

Seems like you could build a query...

Attached is the query that I use. Hope that helps you.

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

On Wed, Dec 13, 2017 at 9:54 AM, Melvin Davidson <melvin6925@gmail.com>
wrote:

On Wed, Dec 13, 2017 at 10:20 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Jeremy Finzel <finzelj@gmail.com> writes:

It looks like the very useful dependency tree shown when using DROP

CASCADE

is written in C in dependency.c, but there is no way to leverage this
within Postgres to actually query an object's dependencies. Can we get
this somehow as a Postgres client in SQL?

Thanks Tom, I am working on this.

Seems like you could build a query for that easily enough using a
recursive union over pg_depend plus pg_describe_object() to produce
text descriptions of the entries.

regards, tom lane

Jeremy ,

per Tom

Seems like you could build a query...

Attached is the query that I use. Hope that helps you.

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

I appreciate that, Melvin. However, this doesn't do the recursive part.
It doesn't show me type or function dependencies, for example:

CREATE TEMP TABLE foo1 (id int);
CREATE TEMP TABLE foo2 (id int);
CREATE VIEW pg_temp.foo3 AS
SELECT f.id, f2.id AS id2
FROM foo1 f CROSS JOIN foo2 f2;
CREATE VIEW pg_temp.foo4 AS
SELECT f.id, f2.id AS id2
FROM foo1 f CROSS JOIN foo3 f2;

CREATE FUNCTION foo() RETURNS SETOF foo1 AS 'SELECT * FROM foo1;' LANGUAGE
SQL;

Your function only shows:
schema | parent | dep_schema | dependent | type
-----------+-----------+------------+-----------+-------
pg_temp_4 | foo1 | pg_temp_4 | foo3 | view
pg_temp_4 | foo1 | pg_temp_4 | foo4 | view
pg_temp_4 | foo2 | pg_temp_4 | foo3 | view
pg_temp_4 | foo3 | pg_temp_4 | foo4 | view

Thanks,
Jeremy

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

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@alvh.no-ip.org>
wrote:

On Fri, Dec 15, 2017 at 6:44 AM, Jeremy Finzel <finzelj@gmail.com> wrote:

Here is my current effort. I would love feedback in case I've missed
something.

Perhaps you'll find the version on the wiki useful as reference, it's an
older version of the dependencies, and can be found at
https://wiki.postgresql.org/wiki/Pg_depend_display​

Perhaps you'll find the version on the wiki useful as reference, it's an
older version of the dependencies, and can be found at
https://wiki.postgresql.org/wiki/Pg_depend_display​

This is where I started. However, I noticed that it do everything I need.
As I said in my original post, as an example if I have a function foo()
that returns type setof foo where foo is a foreign table, the
pg_depend_display functions shows no connection between the function foo()
and the type foo to the table foo.

On Sun, Dec 17, 2017 at 3:31 PM, Jeremy Finzel <finzelj@gmail.com> wrote:

Perhaps you'll find the version on the wiki useful as reference, it's an

older version of the dependencies, and can be found at
https://wiki.postgresql.org/wiki/Pg_depend_display​

This is where I started. However, I noticed that it do everything I
need. As I said in my original post, as an example if I have a function
foo() that returns type setof foo where foo is a foreign table, the
pg_depend_display functions shows no connection between the function foo()
and the type foo to the table foo.

Sorry - Haste makes waste - I meant to say "I noticed that it *doesn't* do
everything I need".