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

Dependency tree to tie type/function deps to a table

From

Jeremy Finzel

Date:

13 December 2017, 21:07:16

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?

One of the problems we face over and over is that we want precisely this list of dependencies, for example so that we can recreate a base table with all of the dependencies on top of it.

The pg_depend_display is not perfect for example to tie together functions that have table dependencies. Currently, 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.

Is there any way we can get that dependency.c code in a consumable form inside postgres, for example as an ordered JSON list of objects as dependencies to be recreated that could be passed to pg_dump in order to recreate the objects in order after recreating a table?

Really appreciate any feedback.

Thanks!

Jeremy

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

From

Tom Lane

Date:

13 December 2017, 21:20:31

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

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

From

Melvin Davidson

Date:

13 December 2017, 21:54:59

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.

Attachment

table_dependents.sql

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

From

Jeremy Finzel

Date:

14 December 2017, 00:39:57

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

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

From

Alvaro Herrera

Date:

14 December 2017, 01:31:24

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

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

From

Jeremy Finzel

Date:

15 December 2017, 20:44:38

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,

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:

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

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

From

bricklen

Date:

16 December 2017, 00:57:53

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

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

From

Jeremy Finzel

Date:

18 December 2017, 03:31:06

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.

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

From

Jeremy Finzel

Date:

18 December 2017, 03:32:00

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".