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

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