sql - 忽略并发插入中的错误

Question

我有一个字符串向量 data，其中包含要插入到名为 foos 的表中的项目。 data 中的某些元素可能已存在于表中，因此我必须注意这些元素。

我使用的解决方案首先将 data 向量转换为虚拟表 old_and_new；然后它构建虚拟表 old，其中包含 foos 中已经存在的元素；然后，它用元素构造虚拟表 new这真的是新的。最后，它将新元素插入到表 foos 中。

WITH   old_and_new AS (SELECT unnest ($data :: text[]) AS foo),
       old AS (SELECT foo FROM foos INNER JOIN old_and_new USING (foo)),
       new AS (SELECT * FROM old_and_new EXCEPT SELECT * FROM old)
INSERT INTO foos (foo) SELECT foo FROM new

这在非并发设置中工作正常，但如果并发线程则失败尝试同时插入相同的新元素。我知道我可以解决这个问题通过将隔离级别设置为 serializable，但这非常笨拙。

还有其他方法可以解决这个问题吗？如果有办法告诉 PostgreSQL 忽略 INSERT 错误是安全的...

Answer 1

Is there some other way I can solve this problem?

有很多，但没有一个是万能的...

您不能像执行 select for update 一样锁定插入，因为行尚不存在。

您可以锁定整个表，但这比序列化您的事务更难。

您可以使用建议锁，但要特别小心死锁。对新 key 进行排序，以便以一致的、可预测的顺序获得锁。 (希望对 PG 的源代码更了解的人插话，但我猜测在可序列化隔离级别中使用的谓词锁恰恰就是这样做的。)

在纯 sql 中，您还可以使用 do 语句逐行循环，并在错误发生时捕获它们:

• http://www.postgresql.org/docs/9.2/static/sql-do.html
• http://www.postgresql.org/docs/9.2/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

类似地，您可以创建一个复杂的 upsert 函数并为每条数据调用一次...

如果您在应用级别构建 $data，您可以一个接一个地运行插入并忽略错误。

而且我确定我忘记了一些额外的选项...