Skip site navigation (1) Skip section navigation (2)

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

From: Gavin Flower <GavinFlower(at)archidevsys(dot)co(dot)nz>
To: bricklen <bricklen(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Finding referecing and referenced tables, adaptation from David Fetter's solution
Date: 2011-07-30 10:17:05
Message-ID: 4E33DA21.3010909@archidevsys.co.nz (view raw or flat)
Thread:
Lists: 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

In response to

Responses

pgsql-general by date

Next:From: Gavin FlowerDate: 2011-07-30 11:49:18
Subject: Re: Finding referecing and referenced tables, adaptation from David Fetter's solution
Previous:From: Deniz AtakDate: 2011-07-30 09:01:24
Subject: ERROR: could not read block 4707 of relation 1663/16384/16564: Success

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group