基于PostgreSQL的Closure Table维护与节点谱系查询方案问询

问题说明 #

当前location_closure闭包表通过触发器维护时，插入子节点只会生成父节点到子节点的单层级关联，缺失祖父及以上层级的关联记录，导致无法完整查询节点的所有祖先及对应深度。

修复后的表结构 #

保留原表结构，建议为location_closure添加唯一约束避免重复记录：

CREATE TABLE location_closure (
    parent VARCHAR(100),
    child VARCHAR(100),
    depth2 INT,
    PRIMARY KEY (parent, child) -- 添加唯一约束，防止重复关联
);

CREATE TABLE location (
    id VARCHAR NOT NULL DEFAULT md5(random()::text),
    name VARCHAR(100),
    startdate TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    within VARCHAR(100),
    PRIMARY KEY (id)
);

修复后的触发器函数与触发器 #

修改触发器函数，确保插入节点时生成自身关联以及所有祖先到当前节点的关联：

CREATE OR REPLACE FUNCTION location_trigger_function() RETURNS TRIGGER AS $$
BEGIN
    -- 所有节点都需要插入自身到自身的深度0记录
    INSERT INTO location_closure (parent, child, depth2)
    VALUES (NEW.id, NEW.id, 0);

    -- 若存在父节点，插入父节点的所有祖先到当前节点的关联
    IF NEW.within IS NOT NULL AND NEW.within != '' THEN
        INSERT INTO location_closure (parent, child, depth2)
        SELECT p.parent, NEW.id, p.depth2 + 1
        FROM location_closure p
        WHERE p.child = NEW.within;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER location_trigger
AFTER INSERT ON location
FOR EACH ROW
EXECUTE PROCEDURE location_trigger_function();

插入测试数据 #

INSERT INTO location (name) VALUES ('Smith County');
INSERT INTO location (name) VALUES ('Geneva County');
INSERT INTO location (name, within) SELECT 'Paradise City', id FROM location WHERE name LIKE '%Smith County%' LIMIT 1;
INSERT INTO location (name, within) SELECT 'Local Mart', id FROM location WHERE name LIKE '%Paradise City%' LIMIT 1;

查询节点的所有祖先及深度 #

以查询Local Mart的所有祖先为例：

SELECT l.name AS ancestor_name, lc.depth2
FROM location_closure lc
JOIN location l ON lc.parent = l.id
WHERE lc.child = (SELECT id FROM location WHERE name = 'Local Mart');

查询结果会完整返回：

• Local Mart（深度0）
• Paradise City（深度1）
• Smith County（深度2）

逻辑说明 #

1. 所有节点插入时都会生成自身到自身的深度0记录，这是闭包表的基础规则，确保节点能匹配自身
2. 当节点存在父节点时，通过查询父节点的全部闭包关联记录（包含父节点自身及所有祖先），将这些节点与当前新节点建立关联，深度在原基础上加1，一次性生成所有层级的关联关系

内容的提问来源于stack exchange，提问作者user2057674