PostgreSQL设计的依赖树没有循环依赖

发布时间：2020-12-13 15:59:46 所属栏目：百科来源：网络整理

导读：我有一个表,称之为EVENTS,其中每行可以依赖于表中的0个或更多其他行.我需要一种表示这种关系的方法,这种方式也可以防止循环依赖(即一组事件导回同一组中的事件). 我目前在EVENTS外部有一个链接表,称之为EVENTS_DEP.此表将依赖行链接到它们所依赖的行,并允许

我有一个表,称之为EVENTS,其中每行可以依赖于表中的0个或更多其他行.我需要一种表示这种关系的方法,这种方式也可以防止循环依赖(即一组事件导回同一组中的事件).

我目前在EVENTS外部有一个链接表,称之为EVENTS_DEP.此表将依赖行链接到它们所依赖的行,并允许在一行上存在多个依赖项.如何使用这样的表防止循环依赖？

注意：如果完全可以仅使用数据库设计(不使用脚本,触发器等),这将是理想的选择.

此外,如果只能使用触发器进行此操作,请告诉我应该运行什么类型的触发器(即在什么事件上)(插入时,可能？).

解决方法

INSERT触发器检查这一点.

假设有以下表结构

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)
);

将需要以下INSERT触发器

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();

它的作用是在父A和子B之间添加新链接时,它使用A WITH RECURSIVE查询来查找A的所有祖先.B不应该是其中之一.

UPDATE触发器更难,因为当执行触发器时,旧链接仍然存在,因此INSERT触发器的测试在用于UPDATE时可能会出现误报.

因此,对于UPDATE,我们需要添加一些额外的条件来隐藏旧数据.

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();

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!