Обсуждение: Dependency tree to tie type/function deps to a table

Поиск
Список
Период
Сортировка

Dependency tree to tie type/function deps to a table

От
Jeremy Finzel
Дата:
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

От
Tom Lane
Дата:
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

От
Melvin Davidson
Дата:


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.
Вложения

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

От
Jeremy Finzel
Дата:
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

От
Alvaro Herrera
Дата:
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

От
Jeremy Finzel
Дата:
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_objident_typeident_identityclassidobjidview_namern
function foo6()functionpublic.foo6()12552418219
composite type foo9composite typepublic.foo912592418711
view foo5viewpublic.foo5261824180foo58
function foo()functionpublic.foo()1255241816
materialized view foo8materialized viewpublic.foo8261824186foo84
view foo4viewpublic.foo4261824176foo43
view foo3viewpublic.foo3261824172foo32

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

От
bricklen
Дата:

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

От
Jeremy Finzel
Дата:
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

От
Jeremy Finzel
Дата:
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".