Re: Dependency tree to tie type/function deps to a table - Mailing list pgsql-general

From Jeremy Finzel
Subject Re: Dependency tree to tie type/function deps to a table
Date
Msg-id CAMa1XUiG+=Tw5UJ-F680dNHFQc2jr=pU0VJxk677Fg4D=ps4gw@mail.gmail.com
Whole thread Raw
In response to Re: Dependency tree to tie type/function deps to a table  (Alvaro Herrera <alvherre@alvh.no-ip.org>)
Responses Re: Dependency tree to tie type/function deps to a table
List pgsql-general
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

pgsql-general by date:

Previous
From: Laurenz Albe
Date:
Subject: Re: User-defined print format for extension-defined types in psqloutput
Next
From: George Woodring
Date:
Subject: Clarification on PL/pgSQL query plan caching