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
I just realized that the 3rd & 4th line will always show the same values
as the 1st & 2nd lines, as only the column headings change! Is this
intentional?
c.oid AS origin_id,
c.oid::regclass::text AS origin_table,
c.oid AS referencing_id,
c.oid::regclass::text AS referencing_table,
Cheers,
Gavin
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
