PostgreSQL design of dependency tree without circular dependencies

Question

I have a table, call it EVENTS, where each row can depend on 0 or more other rows in the table. I need a way of representing this relationship that also prevents circular dependencies (i.e. a group of events leading back into an event in that same group).

I currently have a link table external to EVENTS, call it EVENTS_DEP. This table links dependent rows to the rows they depend on and allows for multiple dependencies on one row. How would I prevent circular dependencies using such a table?

NOTE: if it is at all possible to do this only with the database design (not with scripts, triggers, etc.), this would be ideal.

Also, if this can only be done using triggers, please let me know what kind of trigger (i.e. on what event) it should be run on (on insert, maybe?).

I see no way todo this without triggers and I'm not sure it is worth the trouble to do it with triggers. This could get very expensive when the chain of dependency gets long and branches much. BTW do you really need N to M relation, 1 to N is not enough? — Eelke, Jul 28 '12 at 06:20
I thought about it some more and had the idea it might become much easier when you constrain the way the links are allowed to change. First don't allow updates on the events_dep table. Second allow only the insertion of links to children who have no children them selves. This is relative cheap to check and would prevent the creation of circular links but would restrict the way your data can change. — Eelke, Jul 28 '12 at 06:39
N to M is what I need because, in my case, each node can be depended on by many other nodes and can depend on many other nodes. A good example of what I am aiming at is the curriculum progression in Kahn Academy, where a tree structure of some sort is set up that defines curriculum dependencies. — Adam, Jul 29 '12 at 01:27
@Eelke Good idea, but this is just the first step of what would be done anyway in the full recursive check. Why not do the rest of the steps while we're at it? — Branko Dimitrijevic, Jul 30 '12 at 09:43
@Branko Dimitrijevic, ok I worked out an INSERT and an UPDATE trigger, see my answer. — Eelke, Jul 30 '12 at 16:32
@Eelke Haven't tried to actually test it, but looks good at the first glance. +1 from me :) — Branko Dimitrijevic, Jul 30 '12 at 17:02

score 11 · Accepted Answer · answered Jul 30 '12 at 16:29

INSERT Trigger to check this.

Assuming the following table structure

CREATE TABLE event (
    id bigserial PRIMARY KEY,
    foo varchar
);

CREATE TABLE event_deps (
    parent bigint REFERENCES event(id),
    child bigint REFERENCES event(id),

    PRIMARY KEY (parent, child),
    CHECK (parent <> child)
);

The following INSERT trigger would be needed

CREATE FUNCTION deps_insert_trigger_func() RETURNS trigger AS $BODY$
    DECLARE
        results bigint;
    BEGIN
        WITH RECURSIVE p(id) AS (
            SELECT parent
                FROM event_deps
                WHERE child=NEW.parent
            UNION
            SELECT parent
                FROM p, event_deps d
                WHERE p.id = d.child
            )
        SELECT * INTO results
        FROM p
        WHERE id=NEW.child;

        IF FOUND THEN 
            RAISE EXCEPTION 'Circular dependencies are not allowed.';
        END IF;
        RETURN NEW;
    END;
$BODY$ LANGUAGE plpgsql;

CREATE TRIGGER before_insert_event_deps_trg BEFORE INSERT ON event_deps
    FOR EACH ROW
    EXECUTE PROCEDURE deps_insert_trigger_func();

What it does is when a new link is added between parent A and child B it uses A WITH RECURSIVE query to find all ancestors of A. B shouldn't be one of them.

The UPDATE trigger is harder because when the trigger is executed to old link is still there so the test from the INSERT trigger could give a false positive when used for UPDATEs.

So for the UPDATE we need to add some extra conditions to hide the old data.

CREATE FUNCTION deps_update_trigger_func() RETURNS trigger AS $BODY$
    DECLARE
        results bigint;
    BEGIN
        WITH RECURSIVE p(id) AS (
            SELECT parent
                FROM event_deps
                WHERE child=NEW.parent
                    AND NOT (child = OLD.child AND parent = OLD.parent) -- hide old row
            UNION
            SELECT parent
                FROM p, event_deps d
                WHERE p.id = d.child
                    AND NOT (child = OLD.child AND parent = OLD.parent) -- hide old row
            )
        SELECT * INTO results
        FROM p
        WHERE id=NEW.child;

        IF FOUND THEN 
            RAISE EXCEPTION 'Circular dependencies are not allowed.';
        END IF;
        RETURN NEW;
    END;
$BODY$ LANGUAGE plpgsql;

CREATE TRIGGER before_update_event_deps_trg BEFORE UPDATE ON event_deps
    FOR EACH ROW
    EXECUTE PROCEDURE deps_update_trigger_func();

Excellent answer, thanks a million for the help. I'll need to begin inserting these things into my database (I've got about fifteen places where variations on this can seriously improve things for me). — Adam, Aug 01 '12 at 18:50

score 1 · Answer 2 · answered Jul 28 '12 at 17:54

It is not possible to do this with SQL engines and without programming triggers. For an SQl engine to support this, it would need to support recursive SQL (aka recursive WITH expressions, or recursive CTE's), as well as reliable support for ASSERTIONs.

Many have support for CTE's/WITH expressions, though perhaps not all of them also support the recursive version of the feature. As for ASSERTIONs otoh, I'm told there is only one systems that supports them, but the implementation is so flawed and bug-ridden that it's laughable to consider using it seriously.

There are systems that allow you to do exactly as you ask, but don't expect to talk SQL to such systems. The authors of such systems take a pride in keeping their babies relational.

PostgreSQL design of dependency tree without circular dependencies

2 Answers2