postgresql递归cte查祖先链必须用on c.parent_id = tp.id（子连父），写反则结果为空或仅同级节点；应使用array拼路径、加where not c.id = any(tp.path)防环，并将排序聚合移至外层select。
PostgreSQL递归CTE里JOIN方向写反了会查不出祖先链
查某节点的完整上级路径（比如“评论→父评论→祖父评论”），必须让递归部分的 JOIN 是子连父，即 ON c.parent_id = tp.id。如果写成 ON c.id = tp.parent_id，结果要么空，要么只返回同级节点——因为逻辑上是在找“和当前节点有相同父ID的兄弟”，不是向上追溯。
常见错误现象：执行后只返回起始节点自己，或返回一堆无关节点。锚点选对了（如 WHERE id = 123），但递归支没真正构成父子闭环。
- 查祖先（向上）：递归支中
FROM categories c JOIN tree_path tp ON c.parent_id = tp.id - 查后代（向下）：递归支中
FROM categories c JOIN tree_path tp ON c.id = tp.parent_id - 方向一旦定错，整个路径就断了，不会报错，但结果不可信
用ARRAY拼路径比字符串更安全，且防注入
用 CONCAT 或 || 拼字符串路径（如 '1/5/23'）看似简单，但存在两个硬伤：一是非法 ID（如 '1/5/abc'）能混入结果；二是做子树判定时只能靠 LIKE '1/5/%'，无法走索引，还容易误匹配（'1/50' 会被 '1/5%' 错抓）。
PostgreSQL 的 integer[] 天然规避这些问题：ARRAY[id] 类型严格、防注入，@> 运算符可直接走 GIN 索引做子树判定。
PostgreSQL 18.4 官方 Ubuntu 安装包现已发布，这是目前最新的稳定版本。推荐通过官方 APT 仓库安装：先执行 sudo apt update 更新索引，再运行 sudo apt install postgresql-18 即可完成部署。新版本引入了异步 I/O 子系统，在顺序扫描与 VACUUM 场景下性能提升显著，同时支持 UUID v7 原生生成函数与虚拟生成列。
- 锚点写法：
ARRAY[id] AS path，类型自动推导为integer[] - 递归拼接用：
tp.path || c.id，不是CONCAT(tp.path, '/', c.id) - 最终可读路径统一在最外层转：
array_to_string(path, '/')，别在 CTE 内提前转字符串
不加防环逻辑会导致查询卡死或爆栈
真实数据常有脏环：A 的 parent_id 是 B，B 的 parent_id 又误设为 A。PostgreSQL 默认不限制递归深度，这种环会让查询一直跑，直到超时或内存耗尽。
最稳妥的防环方式，是在递归支中记录已访问路径，并用 NOT c.id = ANY(tp.path) 拦截重复节点。这比依赖 MAXRECURSION 更可靠，因为环可能出现在任意层级，不是单纯深度问题。
- 必须加：
WHERE NOT c.id = ANY(tp.path)，位置在递归支的WHERE子句里 - 不能只靠外部
LIMIT，那只是截断结果，不阻止循环本身 - 如果表里允许自引用（
parent_id = id），这个条件就是刚需，否则必爆
JOIN关联路径结果时，别在CTE内排序或分组
递归CTE生成的路径结果，常要跟其他表（如商品表、用户表）做 JOIN 关联。这时候容易手痒，在 CTE 内部加 ORDER BY 或 GROUP BY ——但 PostgreSQL 会拒绝执行，报错 recursive reference must be in FROM clause 或直接忽略排序逻辑。
原因很简单：递归CTE的每一轮迭代都依赖前一轮的原始输出，中间加排序/聚合会破坏迭代输入结构。所有后处理操作（排序、去重、字段计算）必须放到最外层 SELECT 中。
- 正确做法：CTE 只负责生成
id,path,level等基础字段；关联、排序、聚合全放外面 - 错误示例：
SELECT ... FROM tree_path ORDER BY level写在 CTE 定义里 → 报错 - 性能影响：提前在 CTE 里
GROUP BY还可能导致路径被意外去重，漏掉同名不同ID的节点
ANY() 判断、后处理全丢到外层——这四点踩错任何一个，递归路径就不可靠。尤其 ANY() 那条，很多人测试时数据干净，上线后遇到脏环才暴露，得提前埋好。
