在Postgres上，如果不使用"INSERT ... ON CONFLICT DO NOTHING"，应该使用什么？

Question

我有以下查询语句，我在postgres 9.5中使用：

INSERT INTO knowledge_state 
(SELECT learnerid learner_id, lo_id FROM qb_lo_tag WHERE qb_id = NEW.qb_id)
ON CONFLICT DO NOTHING ;

不幸的是，我无法在某些服务器上使用postgres 9.5，并且需要将其转换为适用于9.5以前版本的查询。我已经构建了以下查询，但它对我来说似乎更加复杂，我认为可能有更简单的方法。

FOR rows IN SELECT lo_id FROM knowledge_state 
WHERE learner_id = learnerid 
AND lo_id IN (SELECT lo_id FROM qb_lo_tags WHERE qb_id = New.qb_id) LOOP

  INSERT INTO knowledge_state (lo_id, learner_id) SELECT rows.lo_id, learnerid 
WHERE NOT EXISTS (SELECT * FROM knowledge_state WHERE lo_id = rows.lo_id AND learner_id = learnerid);

END LOOP;

我很想听听如何简化此查询的想法。

- Clémentine

Answer 1

不需要循环，只需继续执行你正在做的事情：

INSERT INTO knowledge_state (lo_id, learner_id) 
SELECT  a.lo_id, a.learnerid
FROM qb_lo_tag a
WHERE a.qb_id = NEW.qb_id
and  NOT EXISTS (SELECT * FROM knowledge_state b 
     WHERE b.lo_id = a.lo_id AND b.learner_id = a.learnerid);

当然，您可以在knowledge_state(lo_id, learner_id)上添加索引以加快速度（On Conflict意味着唯一约束或其他约束条件，而唯一约束条件则意味着索引）。

- tpdi

---

错误。在存在并发事务的情况下，这是不正确的；如果同时运行两个事务，它仍然可能引发重复键错误。您必须首先使用LOCK TABLE或在plpgsql中使用LOOP来捕获重复键异常并重试。这实际上与upsert相同的问题。 - Craig Ringer

@CraigRinger，我的查询版本是否不受您上述描述的问题影响？ - Clémentine

如果在两个并发会话中同时执行INSERT ... WHERE NOT EXISTS (SELECT ...)，那么WHERE NOT EXISTS的条件将对这两个会话都成立，因为它们都找不到任何行符合条件。因此，它们都将继续进行INSERT操作。其中一个会话将失败。这是事务可见性规则的后果，具体来说是为了防止脏读，以及唯一性索引部分违反事务隔离以确保唯一性。你必须要么锁定表，要么使用 plpgsql 循环来捕获 unique_violation 并重试。 - Craig Ringer