Re: Finding referecing and referenced tables, adaptation from David Fetter's solution

From: Gavin Flower
Subject: Re: Finding referecing and referenced tables, adaptation from David Fetter's solution
Date: ,
Msg-id: 4E33C171.6080309@archidevsys.co.nz
(view: Whole thread, Raw)
In response to: Finding referecing and referenced tables, adaptation from David Fetter's solution  (bricklen)
List: pgsql-general


On 30/07/11 10:45, bricklen wrote:
> A coworker of mine* was looking for a way to quickly and easily be
> able to tell which tables were referencing particular table(s) she
> wanted to load (for unit testing). Using the examples from David
> Fetter**, she submitted a revised version that seems to work quite
> well. With her permission, I have posted her changes and comments.
>
> Here is what she had to say about her changes (lightly-edited to mask
> table names etc)
>
> "The queries linked in the original post by David Fetter do not appear
> to take into account cases where table A references table B, and B
> references A.  (Or longer cycles.) In our environment, we have a lot
> of those.
> Using the original query as a cheat sheet, I wrote a query that
> essentially takes as parameter the name of the table whose "ancestors"
> you want to find, and comes up with a list of everything it depends
> on, but stops as soon as it detects a cycle.
>
> To get a list of all the FK relationships that start at the table of interest:
>
> SELECT referenced_table, depth, chain FROM table_dependencies WHERE
> origin_table = '<table name>';
>
> To get a simple list of all tables that a given table requires due to
> FK dependencies (which is what I need for my unit tests):
>
> SELECT DISTINCT referenced_table FROM table_dependencies WHERE
> origin_table = '<table name>';
>
> To get a partial ordering of the dependencies -- which should tell you
> in what order the tables should be populated in order to avoid FK
> violations -- I think you can do this:
>
> SELECT referenced_table, MIN(depth) AS path_length
> FROM table_dependencies
> WHERE origin_table = '<table name>'
> GROUP BY referenced_table
> ORDER BY path_length DESC;"
>
> -- Here is the view she came up with:
>
> CREATE OR REPLACE VIEW table_dependencies AS (
> WITH RECURSIVE t AS (
>      SELECT
>          c.oid AS origin_id,
>          c.oid::regclass::text AS origin_table,
>          c.oid AS referencing_id,
>          c.oid::regclass::text AS referencing_table,
>          c2.oid AS referenced_id,
>          c2.oid::regclass::text AS referenced_table,
>          ARRAY[c.oid::regclass,c2.oid::regclass] AS chain
>      FROM pg_catalog.pg_constraint AS co
>      INNER JOIN pg_catalog.pg_class AS c
>      ON c.oid = co.conrelid
>      INNER JOIN pg_catalog.pg_class AS c2
>      ON c2.oid = co.confrelid
> -- Add this line as "parameter" if you want to make a one-off query
> -- or a function instead of a view
> --        WHERE c.oid::regclass::text = '<table name>'
>
>      UNION ALL
>      SELECT
>          t.origin_id,
>          t.origin_table,
>          t.referenced_id AS referencing_id,
>          t.referenced_table AS referencing_table,
>          c3.oid AS referenced_id,
>          c3.oid::regclass::text AS referenced_table,
>          t.chain || c3.oid::regclass AS chain
>      FROM pg_catalog.pg_constraint AS co
>      INNER JOIN pg_catalog.pg_class AS c3
>      ON c3.oid = co.confrelid
>      INNER JOIN t
>      ON t.referenced_id = co.conrelid
>      WHERE
>          -- prevent infinite recursion by pruning paths where the last entry in
>          -- the path already appears somewhere else in the path
>          NOT (
>              ARRAY[ t.chain[array_upper(t.chain, 1)] ] -- an array
> containing the last element
>              <@                                        -- "is contained by"
>              t.chain[1:array_upper(t.chain, 1) - 1]    -- a slice of the chain,
>                                                        -- from element 1 to n-1
>          )
> )
> SELECT  origin_table,
>          referenced_table,
>          array_upper(chain,1) AS "depth",
>          array_to_string(chain,',') as chain
> FROM t
> );
>
>
> If anyone has any fixes or changes, or knows of a better way to get
> the referencers/referencees, we'd be interested in hearing about them.
>
> * Jenny van Hoof deserves the credit for the changes
> ** http://people.planetpostgresql.org/dfetter/index.php?/archives/27-Key-Tree.html
>
Interesting, this is an area I just starting to get into. I have run the
code without error message in both pg9.1beta3 and pg8.4.8 - I will
investigate firther.

By the way, what version of pg are you using?  As I noticed somethings
that work in 9.0 do not work in either 8.4 or 9.1 - but I haven't had
time to investigate why yet.


Cheers,
Gavin


pgsql-general by date:

From: Tomas Vondra
Date:
Subject: Re: Statistics about Streaming Replication deployments in production
From: Sim Zacks
Date:
Subject: Re: eval function