CODEWITHSUNDEEP
sqlpostgresqlplpgsqlpostgresql-9.1circular-reference
PL-SQL Developer1000
PL-SQL Developer1000

Reputation: 133

Finding circular references within PostgreSQL table?

I have one table with attributes (ID int, SourceID int, TargetID int, TargetType int) 

ID SourceID TargetID
---------------------
1   123       456  
2   456       789  
3   1         123  
4   456        1   
5   2          1

I want to find out all circular references. I want to write PL/pgsql function for this.

Here circular reference for ID 4 = 456 1 123 456

I want to find such instances. Can anyone suggest me how to proceed for this.

Upvotes: 6

Views: 2813

Answers (3)

Ilya Ordin
Ilya Ordin

Reputation: 127

CREATE TABLE members (
  id serial PRIMARY KEY,
  reports_to_id int REFERENCES members(id) ON DELETE RESTRICT
);

CREATE OR REPLACE FUNCTION circular_reference() RETURNS trigger AS $$
  DECLARE circular_ref_exists boolean := false;
  BEGIN
    IF (NEW.reports_to_id = NEW.id)
    THEN
      RAISE EXCEPTION 'A self reference has been detected';
    END IF;

    WITH RECURSIVE cte AS (
      SELECT * FROM members
      WHERE members.id = NEW.reports_to_id
      UNION
      SELECT members.* FROM cte
      INNER JOIN members ON
        members.id = cte.reports_to_id AND
        members.id <> NEW.id
    )
    SELECT true INTO circular_ref_exists FROM cte WHERE reports_to_id = NEW.id;

    IF (circular_ref_exists)
    THEN
      RAISE EXCEPTION 'A circular reference has been detected';
    END IF;

    RETURN NEW;
  END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER circular_reference
    BEFORE UPDATE ON members
    FOR EACH ROW
    EXECUTE FUNCTION circular_reference();

Upvotes: 1

Andre Silva
Andre Silva

Reputation: 4928

This is similar to How to prevent a self-referencing table from becoming circular (SQL-Server version), which poses the question using a didatic example of employee/manager table.

Beyond a function to detect circular references I was interested having a trigger to prevent such data to be inserted or updated in the database.

Here is the code PLpgSQL/PostgreSQL I adapted from mellamokb's answer:

-- example of table structure
CREATE TABLE employee (
   ID INTEGER NOT NULL,
   ManagerID INTEGER,
   CONSTRAINT a PRIMARY KEY (ID),
   CONSTRAINT b FOREIGN KEY (ManagerID) REFERENCES employee (ID))

-- function to be used inside trigger
CREATE OR REPLACE FUNCTION CircularReference()
RETURNS TRIGGER
AS
$$ 
DECLARE _CircularReferenceExists BIT(1) := 0;

BEGIN  

    WITH RECURSIVE cte AS (
        SELECT E.* FROM employee E WHERE E.ID = new.ManagerID
        UNION -- union instead of union all to prevent infinite looping.
        SELECT E.* FROM employee E JOIN cte C ON C.ManagerID = E.ID AND E.ID <> new.ManagerID
    )
    SELECT count(*) INTO _CircularReferenceExists FROM cte C WHERE C.ManagerID = new.ManagerID;

    IF (SELECT _CircularReferenceExists <> B'0')
    THEN
    RAISE EXCEPTION 'Circular Reference found: ManagerID = %', new.ManagerID;
    END IF;

RETURN NEW;       
END
$$ LANGUAGE plpgsql;

-- trigger
CREATE TRIGGER CircularReference
AFTER INSERT OR UPDATE
ON employee
FOR EACH ROW
EXECUTE PROCEDURE CircularReference();

If one already have data in the database just create the trigger and use an UPDATE statement to make it start detecting circular references.

UPDATE employee SET ID = ID;

Upvotes: 4

klin
klin

Reputation: 121634

This can be done with the recursive function below. The function uses intarray extension.

create extension intarray;

The first element of int array arr is an id. The rest of the array contains consecutive references source -> target.

If the second and the last elements of the array are equal, then a circular reference was found. (1)

We must look for inner circular references and eliminate them (or we'll finish with stack overflow). (2)

create or replace function find_cref(arr int[])
returns setof int[] language plpgsql
as $$
declare
    vlen int = #arr;
    vtarget int;
begin
    if arr[2] = arr[vlen] then                                 -- (1)
        return query select arr;
    else
        if #uniq(sort(subarray(arr, 2)))+ 1 = vlen then        -- (2)
            for vtarget in
                select target from the_table where source = arr[vlen]
            loop
                return query select find_cref (arr+ vtarget);
            end loop;
        end if;
    end if;
end $$;

select c[1] id, subarray(c, 2) cref 
from (
    select find_cref(array[id, source, target]) c
    from the_table) x

Upvotes: 1

Related Questions