3

I want to detect potential cycles in a hierarchy. I have three tables, each have one parent, and one child column:

Table1's parents are Table2's children and Table2's parents are Table3's children

Table1 contains some nodes (in column child) and their parents (in column parent); Table2 contains all the parents of Table1 (in column child) and their parents (in column parent), and so on.

For example if A is a child of B, and B is a child of C and C is a child of A, then I have a cycle.

Is it possible to detect the cycles using sql commands?

geek2000
  • 451
  • 5
  • 18
  • Which DBMS are you using? –  Sep 18 '16 at 08:35
  • I am using Postgres. Is that what you mean? – geek2000 Sep 18 '16 at 08:38
  • What so you mean by cycles? Will you be able to elobrate – Jim Macaulay Sep 18 '16 at 08:39
  • Do you want to detect cycles _between_ the tables (if yes, why isn't this stored in a single table?) Or just in one table? [edit] your question and add the **real** definition of the tables (as `create table` statements), some sample data and the expected output based on that data. [_Formatted_](http://stackoverflow.com/editing-help#code) **text** please, [no screen shots](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557) –  Sep 18 '16 at 08:40
  • For example if A is a child of B, and B is a child of C and C is a child of A, then I have a cycle. – geek2000 Sep 18 '16 at 08:41
  • I stored them in separate tables because I thought it might make the cycle detection process easier. That's why! – geek2000 Sep 18 '16 at 08:43
  • 2
    Don't de-normalize your model just because you _think_ it would make things easier. –  Sep 18 '16 at 08:58
  • If I dump all the parent-child relationships in one table, is there a way I can detect the cycles? – geek2000 Sep 18 '16 at 09:01
  • 1
    See here: https://stackoverflow.com/questions/26671612/prevent-and-or-detect-cycles-in-postgres or here: https://stackoverflow.com/questions/25194553/detecting-cycles-in-a-recursive-query –  Sep 18 '16 at 09:03

3 Answers3

7

Here's a solution that works with arbitrary depth.

Store all your relationships in one table:

   Table t
Parent | Child
------ | -----
B      | A
C      | B
A      | C
E      | D
F      | E

Then you can use this WITH RECURSIVE query to find cycles:

WITH RECURSIVE working(parent, last_visited, already_visited, cycle_detected) AS (
  SELECT parent, child, ARRAY[parent], false FROM t
  UNION ALL
  SELECT t.parent, t.child, already_visited || t.parent, t.parent = ANY(already_visited)
  FROM t
  JOIN working ON working.last_visited = t.parent
  WHERE NOT cycle_detected
)
SELECT parent, already_visited FROM working WHERE cycle_detected

Fiddle

It will give you the parents that are part of a cycle, and also the cycle they are in:

A | A,C,B,A
B | B,A,C,B
C | C,B,A,C

It works like this (because that is what the keyword RECURSIVE instructs Postgres to do):

  1. Run the first SELECT, selecting all entries from table t and placing them in a temporary table named working.
  2. Then run the second SELECT, joining the working table with table t to find the children of each entry. Those children are added to the array of already seen children.
  3. Now run the second SELECT again and again, as long as entries are added to the working table.
  4. A cycle is detected when one of the entries visits a child that it has visited before (t.parent = ANY(already_visited)) in this case cycle_detected is set to true and no more children are added to the entry.
AndreKR
  • 32,613
  • 18
  • 106
  • 168
  • Thank you for the great stuff and for your comprehensive explanation. Very useful. – geek2000 Sep 18 '16 at 11:36
  • Also, here's the official Postgres docs that I believe do a great job of explaining how the RECURSIVE queries work. https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-SELECT – Gurjeet Singh Nov 30 '20 at 05:41
  • Soon PostgreSQL will support [`CYCLE`](https://stackoverflow.com/a/66050763/5070879) clause which simplify the process – Lukasz Szozda Feb 04 '21 at 17:36
2

The way you have structured your tables right now, the following SQL should work:

SELECT * FROM Table1
INNER JOIN Table2 on Table1.child = Table2.parent
INNER JOIN Table3 on Table2.child = Table3.parent
WHERE Table1.parent = Table3.child;
mateuszlo
  • 1,309
  • 1
  • 11
  • 10
0

There are very strange references between tables in your task. Yet it is my approach to check existing loop.

Example for table1:

CREATE OR REPLACE FUNCTION fn_table1_check() RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
BEGIN
  PERFORM 1 FROM table2
    JOIN table3 ON table3.parent=table2.child
  WHERE table2.parent=NEW.child AND table3.child=NEW.parent
  LIMIT 1;
  IF FOUND THEN
    RAISE EXCEPTION 'Found recursive loop!';
  END IF;
  RETURN NEW;
END;
$$;
CREATE TRIGGER tg_table1_check BEFORE INSERT OR UPDATE ON table1 FOR EACH ROW EXECUTE PROCEDURE fn_table1_check();
Kostya Zhevlakov
  • 257
  • 1
  • 5