Ever wondered what tables have foreign keys to a given table, and which ones to those, and so on?
Read on!
We'll use recursion (see recursion), implemented as a CTE, which is short for
Common Table Expression.
To start the recursion, we'll need the tables which have no foreign keys to other tables.
We go to pg_class and find tables,
WITH RECURSIVE t AS (
SELECT
c.oid,
c.oid::regclass AS "table",
ARRAY[c.oid::regclass] AS chain
FROM
pg_catalog.pg_class c
JOIN
pg_catalog.pg_namespace n -- Namespaces
ON (
c.relkind = 'r' -- Only care about actual tables
AND
c.relnamespace = n.oid
Now trim away the ones we couldn't have created.
AND
n.nspname NOT IN ('pg_catalog','information_schema')
AND
n.nspname !~ '^pg_toast'
)
Next, we eliminate the tables to which foreign keys point.
AND -- Only want tables to which no foreign keys point.
NOT EXISTS (
SELECT 1 FROM pg_catalog.pg_constraint
WHERE c.oid = conrelid
AND contype='f'
)
Next, the recursion step. We're looking for tables that
do point to
the previous tables, and so on.
UNION ALL
SELECT
conrelid,
oid::regclass,
t.chain || conrelid::regclass
FROM
pg_catalog.pg_constraint c
JOIN
t ON t.oid=c.confrelid AND contype='f'
)
We now have our tree. Let's display it, starting from the roots and going out
to the leaves. Just for fun, we can have a depth counter.
SELECT
array_upper(chain,1) AS "depth",
chain
FROM t
ORDER BY
chain;
And presto! All the dependencies for every table! Here's that query in its
entirety.
WITH RECURSIVE t AS (
SELECT
c.oid,
c.oid::regclass AS "table",
ARRAY[c.oid::regclass] AS chain
FROM
pg_catalog.pg_class c
JOIN
pg_catalog.pg_namespace n
ON (
c.relkind = 'r'
AND
c.relnamespace = n.oid
AND
n.nspname NOT IN ('pg_catalog','information_schema')
AND
n.nspname !~ '^pg_toast'
)
AND
NOT EXISTS (
SELECT 1 FROM pg_catalog.pg_constraint
WHERE c.oid = conrelid
AND contype='f'
)
UNION ALL
SELECT
conrelid,
oid::regclass,
t.chain || conrelid::regclass
FROM
pg_catalog.pg_constraint c
JOIN
t ON t.oid=c.confrelid AND contype='f'
)
SELECT
array_upper(chain,1) AS "depth",
chain
FROM t
ORDER BY
chain;
As a bonus, we can use windowing functions to find orphan tables. We do this
by observing that when we put the trees in lexical order, if a root node is
followed immediately by another root node, it's an orphan. Similarly, if a
root node is followed by nothing, i.e. it's the last one, it's also an orphan.
The recursion part is the same as the above, so we'll skip down to the SELECT
query.
SELECT
(
array_upper(chain,1) = 1
AND
(
array_upper(lead(chain,1) OVER w,1) = 1
OR
lead(chain,1) OVER w IS NULL
)
) AS "orphan",
chain
FROM t
WINDOW w AS (ORDER BY chain);
Well, that's a start. We've marked off the ones that are orphans, but we
can't do a conditional on the windowing function.
Hey! I have an idea. Let's put that in another CTE,
then do
the query.
WITH RECURSIVE t AS (
SELECT
c.oid,
c.oid::regclass AS "table",
ARRAY[c.oid::regclass] AS chain
FROM
pg_catalog.pg_class c
JOIN
pg_catalog.pg_namespace n
ON (
c.relkind = 'r'
AND
c.relnamespace = n.oid
AND
n.nspname NOT IN ('pg_catalog','information_schema')
AND
n.nspname !~ '^pg_toast'
)
AND
NOT EXISTS (
SELECT 1 FROM pg_catalog.pg_constraint
WHERE c.oid = conrelid
AND contype='f'
)
UNION ALL
SELECT
conrelid,
oid::regclass,
t.chain || conrelid::regclass
FROM
pg_catalog.pg_constraint c
JOIN
t ON t.oid=c.confrelid AND contype='f'
),
t2 AS (
SELECT
(
array_upper(chain,1) = 1
AND
(
array_upper(lead(chain,1) OVER w,1) = 1
OR
lead(chain,1) OVER w IS NULL
)
) AS "orphan",
chain
FROM t
WINDOW w AS (ORDER BY chain)
)
SELECT chain FROM t2 WHERE orphan;
I found six tables in a customer's database that were orphans, and uncovered
at least two bugs in the process. This helped
PostgreSQL Experts help one of our customers. We do full-stack development.