ltree / postgres / postgresql

Dados hierárquicos em postgres

Esta dica tentará responder às seguintes perguntas:

  • Como podemos representar uma árvore de dados no postgres
  • Como podemos consultar com eficiência qualquer nó único inteiro e todos os seus filhos (e filhos dos filhos).

Os dados de teste

Como queremos manter isso simples, assumiremos que nossos dados são apenas um monte de sections. Uma seção tem apenas um namee cada sectionuma tem um único pai section.

Section A
    |--- Section A.1

Section B
    |--- Section B.1
    |--- Section B.1
               |--- Section B.1.1

Usaremos esses dados simples para os exemplos abaixo.

Auto-referência simples

Ao projetar uma tabela autorreferencial (algo que se junta a si mesmo), a escolha mais óbvia é ter algum tipo de parent_idcoluna em nossa tabela que faça referência a si mesmo.

CREATE TABLE section (
    id INTEGER PRIMARY KEY,
    name TEXT,
    parent_id INTEGER REFERENCES section,
);
ALTER TABLE page ADD COLUMN parent_id INTEGER REFERENCES page;
CREATE INDEX section_parent_id_idx ON section (parent_id);

Agora insira nossos dados de exemplo, usando o parent_idpara relacionar os nós:

INSERT INTO section (id, name, parent_id) VALUES (1, 'Section A', NULL);
INSERT INTO section (id, name, parent_id) VALUES (2, 'Section A.1', 1);
INSERT INTO section (id, name, parent_id) VALUES (3, 'Section B', NULL);
INSERT INTO section (id, name, parent_id) VALUES (4, 'Section B.1', 3);
INSERT INTO section (id, name, parent_id) VALUES (5, 'Section B.2', 3);
INSERT INTO section (id, name, parent_id) VALUES (6, 'Section B.2.1', 5);

Isso funciona muito bem para consultas simples, como buscar os filhos diretos da Seção B :

SELECT * FROM section WHERE parent = 3

mas exigirá consultas complexas ou recursivas para questões como buscar todos os filhos e filhos dos filhos da Seção B :

WITH RECURSIVE nodes(id,name,parent_id) AS (
    SELECT s1.id, s1.name, s1.parent_id
    FROM section s1 WHERE parent_id = 3
        UNION
    SELECT s2.id, s2.name, s2.parent_id
    FROM section s2, nodes s1 WHERE s2.parent_id = s1.id
)
SELECT * FROM nodes;

Portanto, respondemos a parte “como construir uma árvore” da pergunta, mas não estamos satisfeitos com a parte “como consultar um nó e todos os seus filhos”.

Digite ltree. (Abreviação de “árvore de rótulo” – eu acho?).

A extensão ltree

A extensão ltree é uma ótima opção para consultar dados hierárquicos. Isso é especialmente verdadeiro para relacionamentos autorreferenciais.

Vamos reconstruir o exemplo acima usando ltree. Usaremos as chaves primárias da página como os “rótulos” em nossos caminhos de ltree e um rótulo especial de “raiz” para denotar o topo da árvore.

CREATE EXTENSION ltree;

CREATE TABLE section (
    id INTEGER PRIMARY KEY,
    name TEXT,
    parent_path LTREE
);

CREATE INDEX section_parent_path_idx ON section USING GIST (parent_path);

Adicionaremos nossos dados novamente, desta vez em vez de usar o idpara o pai, construiremos um caminho de árvore que representa o nó pai.

INSERT INTO section (id, name, parent_path) VALUES (1, 'Section 1', 'root');
INSERT INTO section (id, name, parent_path) VALUES (2, 'Section 1.1', 'root.1');
INSERT INTO section (id, name, parent_path) VALUES (3, 'Section 2', 'root');
INSERT INTO section (id, name, parent_path) VALUES (4, 'Section 2.1', 'root.3');
INSERT INTO section (id, name, parent_path) VALUES (4, 'Section 2.2', 'root.3');
INSERT INTO section (id, name, parent_path) VALUES (5, 'Section 2.2.1', 'root.3.4');

Legal. Portanto, agora podemos fazer uso dos operadores ltree @>e <@responder à nossa pergunta original como:

SELECT * FROM section WHERE parent_path <@ 'root.3';

No entanto, introduzimos alguns problemas.

  • Nossa parent_idversão simples garantiu consistência referencial fazendo uso da REFERENCESrestrição. Perdemos isso ao mudar para os caminhos da ltree.
  • Garantir que os caminhos da ltree sejam válidos pode ser um pouco doloroso e, se os caminhos se tornarem obsoletos por algum motivo, suas consultas podem retornar resultados inesperados ou você pode “órfãos” de nós.

A solução final

Para corrigir esses problemas, queremos um híbrido de nosso original parent_id(para a consistência referencial e simplicidade do relacionamento filho / pai) e nossos caminhos ltree (para poder de consulta / indexação aprimorado). Para conseguir isso, vamos ocultar o gerenciamento do caminho ltree atrás de um gatilho e apenas atualizar a parent_idcoluna.

CREATE EXTENSION ltree;

CREATE TABLE section (
    id INTEGER PRIMARY KEY,
    name TEXT,
    parent_id INTEGER REFERENCES section,
    parent_path LTREE
);

CREATE INDEX section_parent_path_idx ON section USING GIST (parent_path);
CREATE INDEX section_parent_id_idx ON section (parent_id);

CREATE OR REPLACE FUNCTION update_section_parent_path() RETURNS TRIGGER AS $$
    DECLARE
        path ltree;
    BEGIN
        IF NEW.parent_id IS NULL THEN
            NEW.parent_path = 'root'::ltree;
        ELSEIF TG_OP = 'INSERT' OR OLD.parent_id IS NULL OR OLD.parent_id != NEW.parent_id THEN
            SELECT parent_path || id::text FROM section WHERE id = NEW.parent_id INTO path;
            IF path IS NULL THEN
                RAISE EXCEPTION 'Invalid parent_id %', NEW.parent_id;
            END IF;
            NEW.parent_path = path;
        END IF;
        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER parent_path_tgr
    BEFORE INSERT OR UPDATE ON section
    FOR EACH ROW EXECUTE PROCEDURE update_section_parent_path();

Muito melhor.

Mais

Use json e plv8 para trabalhar com dados de árvore

Related Posts