PostgreSQL的Ltree模块是否适合于线程评论?[英] Is PostgreSQL's Ltree module a good fit for threaded comments?

本文是小编为大家收集整理的关于PostgreSQL的Ltree模块是否适合于线程评论?的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我正在考虑使用postgresql ltree module 申请帮助线程注释.我已经看了一段时间来用于线程评论.我认为这将有助于您需要更新节点及其孩子的情况,例如当您想隐藏评论及其答复时.

我在想ltree(或类似的东西),如果它与传统的邻接列表相结合(" comment_id"/" parent_comment_id")将很有用.

在使用ltree之前,我想知道一些事情:

  1. 您还是使用过ltree?它可能称之为"生产准备"?
  2. 如果是这样,您使用什么问题来解决?它做得很好吗?
  3. 您认为这很适合 线程评论系统?
    1. 如果您使用了它,则用于路径的"文本"部分?您是否设置了类似DMOZ示例之类的东西,它们使用了" top.astronomy.cosmology"或将其基于主键" 1.403.29.5"?
    2. 有更好的方法吗?使用嵌套列表方法,我有点紧张 - 我读过的所有内容都表明,更新或插入材料并不是全部(您不必重新排序整个过程吗?).我也不是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>

在路径中使用主键有什么问题吗?我应该在路径中加入节点自己的主键吗?如果我这样做了,在上面放置一个独特的索引以作为约束是有意义的吗?

推荐答案

  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
          ... | ...

其他推荐答案

我建议任何人在SQL中实现层次关系的任何人读取乔·塞尔科(Joe Celko)在SQL中的树木和层次结构.

仅使用parent_id时,遍历任意深度父母链接的效率可能非常低.该书描述了使此访问速度快速访问的技术.

在本系列文章中也可以免费找到一种策略(我碰巧使用):

其他推荐答案

PostgreSQL的8.4版将以WITH和WITH... RECURSIVE表达式将常见的表表达功能带入核心.如果您正在修改旧代码,则可能需要等到释放8.4,因为您不必担心LTREE和新的Core语法之间的任何不兼容.如果您正在使用旧代码,或者不想等待8.4,则可能要确保编写易于翻译到新语法的代码,尤其是当您更改旧模式或设计新语法的情况下一个.

另请参见:

本文地址:https://www.itbaoku.cn/post/597519.html

问题描述

I'm considering using PostgreSQL's Ltree module in my application to help with threaded comments. I've been eying it for a while to use for threaded comments. I figure it would help with cases where you need to update a node and its children, like when you want to hide a comment and its replies.

I'm thinking ltree (or something like it) it would be useful if it was coupled with a traditional adjacency list ("comment_id"/"parent_comment_id").

Before taking the plunge into using ltree, I'm wondering a few things:

  1. Are you, or have you, used ltree? Is it what one might call "production ready"?
  2. If so, what problems did you use it to solve? Did it do a good job?
  3. Do you think it is a good fit for a threaded comment system?
    1. If you used it, what did you use for the "text" part of the path? Did you set up something like the DMOZ example they use "Top.Astronomy.Cosmology" or base it on something like the primary key "1.403.29.5"?
    2. Is there a better way to do this? I'm a bit nervous using a nested list approach--everything I've read suggests that it isn't all to hot with UPDATES or INSERTS (don't you have to reorder the whole thing?). I'm also not a CS major and that kind of data structure is something I might forget in the future. Is anybody using nested lists for comments or something like it?

If it is of any help, here is the schema I'm considering:

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

The "path" column, used by ltree, would look something like:

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

Is there anything wrong with using the primary keys in the path? Should I be including the node's own primary key in the path? If I did, would it make sense to put a unique index on it to serve as a constraint?

推荐答案

  1. Yes and yes;
  2. Hierarchy of sections in a knowledge base (one of the implementations);
  3. Yes;

The definition of one of the tables in question:

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

The "path" column uses the primary key as a label.

A sample of the current contents of that table (regarding the primary key and the "path" column):

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

其他推荐答案

I recommend anyone implementing hierarchical relationships in SQL read Joe Celko's Trees and Hierarchies in SQL for Smarties.

Traversing arbitrary depth parent child links can be very inefficient when using just a parent_id. The book describes techniques that make this access fast.

One strategy (which I happen to use) can also be found for free in this series of articles:

其他推荐答案

Version 8.4 of PostgreSQL will be bringing Common Table Expressions functionality into the core with WITH and WITH... RECURSIVE expressions. If you're modifying old code, you may want to wait until 8.4 is released, as then you won't have to worry about any incompatibilities between Ltree and the new core syntax. If you're working with old code, or do not want to wait for 8.4, you will probably want to make sure you write code that is easily translatable to the new syntax, especially if you're changing an old schema or designing a new one.

See also: