Postgres插入嵌套集时唯一索引冲突问题求助

你遇到的核心问题是：嵌套集插入新节点时，更新后续节点lft/rgt的过程中，唯一索引的即时检查导致临时值重复触发冲突。以下是几个可行的解决办法：

方案1：按降序更新数值（通用兼容） #

唯一索引冲突的根源是更新时小数值先被修改，和未更新的大数值产生重复。从大到小更新lft和rgt，就能避免临时重复的问题。

比如你要在Languages（假设其rgt=3）下插入新节点（lft=4、rgt=5），执行顺序如下：

-- 先更新rgt值，按降序排列，确保大数值先被修改
UPDATE categories SET rgt = rgt + 2 WHERE rgt > 3 ORDER BY rgt DESC;
-- 再更新lft值，同样按降序
UPDATE categories SET lft = lft + 2 WHERE lft > 3 ORDER BY lft DESC;
-- 最后插入新节点
INSERT INTO categories (name, lft, rgt) VALUES ('New Language', 4, 5);

这种方式下，大的lft/rgt先被加2，不会和未更新的小数值产生重叠，全程不会触发唯一索引冲突。

方案2：使用可延迟的唯一约束（部分数据库支持） #

如果你的数据库支持延迟约束检查（比如PostgreSQL、Oracle），可以把lft和rgt的唯一索引改成可延迟约束，让数据库在事务提交时才检查索引，而非每一步更新后立即检查。

以PostgreSQL为例：

-- 删除原有的唯一索引/约束
ALTER TABLE categories DROP CONSTRAINT categories_lft_key;
ALTER TABLE categories DROP CONSTRAINT categories_rgt_key;

-- 添加可延迟的唯一约束
ALTER TABLE categories ADD CONSTRAINT categories_lft_key UNIQUE(lft) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE categories ADD CONSTRAINT categories_rgt_key UNIQUE(rgt) DEFERRABLE INITIALLY DEFERRED;

之后在事务中执行更新和插入操作，中间过程的临时重复不会触发错误，直到事务提交时才会校验最终的唯一性。

方案3：临时禁用索引（不推荐） #

部分数据库支持临时禁用索引（比如MySQL的MyISAM引擎），但这个方法风险较高：大表重建索引耗时极长，且禁用期间索引失效会影响其他查询性能。示例代码如下：

ALTER TABLE categories DISABLE KEYS;
-- 执行更新和插入
UPDATE categories SET rgt = rgt + 2 WHERE rgt > 3;
UPDATE categories SET lft = lft + 2 WHERE lft > 3;
INSERT INTO categories (name, lft, rgt) VALUES ('New Language', 4, 5);
ALTER TABLE categories ENABLE KEYS;

除非是小型测试表，否则不建议使用这个方案。

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