Re: Finding referecing and referenced tables, adaptation from David Fetter's solution
От | Gavin Flower |
---|---|
Тема | Re: Finding referecing and referenced tables, adaptation from David Fetter's solution |
Дата | |
Msg-id | 4E33DA21.3010909@archidevsys.co.nz обсуждение исходный текст |
Ответ на | Finding referecing and referenced tables, adaptation from David Fetter's solution (bricklen <bricklen@gmail.com>) |
Ответы |
Re: Finding referecing and referenced tables, adaptation from David Fetter's solution
|
Список | 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 > 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
В списке pgsql-general по дате отправления:
Предыдущее
От: Gavin FlowerДата:
Сообщение: Re: Finding referecing and referenced tables, adaptation from David Fetter's solution
Следующее
От: Deniz AtakДата:
Сообщение: ERROR: could not read block 4707 of relation 1663/16384/16564: Success