PostgreSQL - 重复的唯一键

Question

在我的 table 上，我有一个标记为 md5 的辅助唯一 key 。插入前先检查MD5是否存在，不存在则插入，如下图:

-- Attempt to find this item
SELECT INTO oResults (SELECT domain_id FROM db.domains WHERE "md5"=oMD5);

IF (oResults IS NULL) THEN

    -- Attempt to find this domain
    INSERT INTO db.domains ("md5", "domain", "inserted") 
        VALUES (oMD5, oDomain, now());

    RETURN currval('db.domains_seq');

  END IF;

这对于单线程插入非常有效，我的问题是当我有两个外部应用程序同时调用我的函数时恰好具有相同的 MD5。我最终遇到的情况是:

App 1:发现 MD5 不存在

应用 2:将此 MD5 插入表中

应用程序 1:转到现在将 MD5 插入表中，因为它认为它不存在，但收到错误，因为在它看到它不存在之后，应用程序 2 立即插入了它。

有没有更有效的方法？

我能否在插入时捕获错误，如果可以，然后选择 domain_id？

提前致谢!

---

这似乎也包含在 Insert, on duplicate update in PostgreSQL? 中

Answer 1

您可以继续尝试插入 MD5 并捕获错误，如果您遇到“违反唯一约束”错误，则忽略它并继续，如果您遇到其他错误，则退出。这样你就可以将重复检查直接推送到数据库，然后你的竞争条件就会消失。

像这样:

• 尝试插入 MD5 值。
  • 如果您遇到独特的违规错误，请忽略它并继续。
  • 如果您遇到其他错误，请退出并投诉。
  • 如果您没有收到错误，则继续。
• 执行SELECT INTO oResults (SELECT domain_id FROM db.domains WHERE "md5"=oMD5) 以提取domain_id。

可能会有一点性能影响，但“正确但有点慢”比“快但有问题”要好。

最终，您可能会遇到更多成功插入的异常。然后你可以尝试在表中插入引用(通过外键)你的 db.domains 并在那里捕获 FK 违规。如果您有 FK 违规，则在 db.domains 上执行旧的“插入并忽略唯一违规”，然后重试导致您 FK 违规的插入。这是相同的基本思想，只是选择可能抛出最少异常的一个并采用它的问题。