PostgreSQL 的 Ltree 模块是否适合线程注释？

Question

我正在考虑在我的应用程序中使用 PostgreSQL 的 Ltree 模块 来提供帮助带有线索评论。 我已经关注它有一段时间了，用于线程评论。 我认为这对于需要更新节点及其子节点的情况会有所帮助，例如当您想要隐藏评论及其回复时。

我认为 ltree （或类似的东西）如果与传统的邻接列表（“comment_id”/“parent_comment_id”）结合起来会很有用。

在开始使用 ltree 之前，我想知道一些事情：

1. 您是否使用过 ltree？ 这就是所谓的“生产就绪”吗？
2. 如果有，您用它解决了什么问题？ 做得好吗？
3. 你认为它很适合 线程评论系统？
4. 如果您使用过它，您使用什么作为路径的“文本”部分？ 您是否设置了类似 DMOZ 示例的内容，他们使用“Top.Astronomy.Cosmology”，还是基于主键“1.403.29.5”之类的内容？
5. 有更好的方法吗？ 我对使用嵌套列表方法有点紧张——我读过的所有内容都表明更新或插入并不那么热（你不需要重新排序整个事情吗？）。 我也不是CS专业的，这种数据结构是我将来可能会忘记的东西。 有人使用嵌套列表进行评论或类似的东西吗？

如果它有任何帮助，这是我正在考虑的模式：

CREATE TABLE comments (
    comment_id SERIAL PRIMARY KEY,
    parent_comment_id int REFERENCES comments(comment_id) ON UPDATE CASCADE ON DELETE CASCADE,
    thread_id int NOT NULL  REFERENCES threads(thread_id) ON UPDATE CASCADE ON DELETE CASCADE,
    path ltree NOT NULL,
    comment_body text NOT NULL,
    hide boolean not null default false
);

ltree 使用的“路径”列看起来像：

<thread_id>.<parent_comment_id_#1>.<parent_comment_id_#2>.<my_comment_id>

在路径中使用主键有什么问题吗？ 我应该在路径中包含节点自己的主键吗？ 如果我这样做了，在其上放置唯一索引作为约束是否有意义？

Answer 1

1. 是的，是的；
2. 知识库中各部分的层次结构（实现之一）；
3. 是的;

相关表之一的定义：

                                                   Table "knowledgebase.section"
           Column           |           Type           |                                  Modifiers
----------------------------+--------------------------+-----------------------------------------------------------------------------
 section_sid                | integer                  | not null default nextval('knowledgebase.section_section_sid_seq'::regclass)
 section                    | character varying        | not null
 description                | character varying        |
 path                       | ltree                    | not null
 is_active                  | boolean                  | not null default true
 role_sid                   | integer                  | not null
 last_modified_by           | integer                  | not null
 creation_datetime          | timestamp with time zone | not null default now()
 last_modification_datetime | timestamp with time zone | not null default now()
 is_expanded                | boolean                  | not null default false
 section_idx                | tsvector                 |
Indexes:
    "section_sid_pkey" PRIMARY KEY, btree (section_sid)
    "section_section_key" UNIQUE, btree (section)
    "idxsection_idx" gist (section_idx)
    "path_gist_idx" gist (path)
Foreign-key constraints:
    "last_modified_by_fkey" FOREIGN KEY (last_modified_by) REFERENCES "user"."role"(role_sid) ON UPDATE CASCADE ON DELETE RESTRICT
    "role_sid_fkey" FOREIGN KEY (role_sid) REFERENCES "user"."role"(role_sid) ON  UPDATE CASCADE ON DELETE RESTRICT
Triggers:
    section_idx_update BEFORE INSERT OR UPDATE ON knowledgebase.section FOR EACH ROW EXECUTE PROCEDURE tsearch2('section_idx', 'section')

“路径”列使用主键作为标签。

该表当前内容的示例（关于主键和“路径”列）：

  section_sid | path
 -------------+-------
           53 | 34.53
           56 | 56
           55 | 29.55
           35 | 35
           54 | 34.54
           37 | 30.37
          ... | ...

Answer 2

我建议任何在 SQL 中实现层次关系的人阅读Joe Celko 为聪明人编写的 SQL 中的树和层次结构。

仅使用parent_id 时，遍历任意深度的父子链接可能会非常低效。 本书描述了可以快速访问的技术。

在本系列文章中也可以免费找到一种策略（我碰巧使用的）：

• 第 1 部分< /a> （查找“SQL 中的树”部分） (回溯链接)
• 第 2 部分 (回溯链接)
• 第 3 部分 (第 3 部分 dbmsmag.com/9605d06.html" rel="nofollow noreferrer">回溯链接)
• 第 4 部分 (回溯链接)

Answer 3

PostgreSQL 8.4 版本将通过 WITH 和 WITH...RECURSIVE 表达式将公共表表达式功能引入核心。 如果您正在修改旧代码，您可能需要等到 8.4 发布，这样您就不必担心 Ltree 和新核心语法之间的任何不兼容。 如果您正在使用旧代码，或者不想等待 8.4，您可能需要确保编写的代码可以轻松转换为新语法，特别是当您正在更改旧模式或设计新模式时一。

另请参阅：

• 关于 WITH 查询和的 8.4 手册条目CTE
• 关于 CTE 8.4 实现的示例文章