PostgreSQL - 重复的唯一键
在我的桌子上，我有一个标记为 md5 的辅助唯一密钥。在插入之前，我检查 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。我最终遇到这样的情况：
应用程序 1：看到 MD5 不存在
应用程序 2：将此 MD5 插入表
应用程序 1：转到现在 将 MD5 插入表，因为它认为它不存在，但收到错误，因为就在它之后看到它没有，App 2 插入了它。
有更有效的方法吗？
我可以捕获插入错误吗？如果是这样，然后选择domain_id？
提前致谢！
在 PostgreSQL 中重复更新时插入？ 中似乎也涵盖了这一点
On my table I have a secondary unique key labeled md5. Before inserting, I check to see if the MD5 exists, and if not, insert it, as shown below:
-- 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;
This works great for single threaded inserts, my problem is when I have two external applications calling my function concurrently that happen to have the same MD5. I end up with a situation where:
App 1: Sees the MD5 does not exist
App 2: Inserts this MD5 into table
App 1: Goes to now Insert MD5 into table since it thinks it doesnt exist, but gets an error because right after it seen it does not, App 2 inserted it.
Is there a more effective way of doing this?
Can I catch the error on insert and if so, then select the domain_id?
Thanks in advance!
This also seems to be covered at Insert, on duplicate update in PostgreSQL?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您可以继续尝试插入 MD5 并捕获错误，如果您收到“唯一约束违规”错误，则忽略它并继续，如果您收到其他错误，则退出。这样，您就可以将重复检查直接推送到数据库，并且竞争条件就会消失。
像这样的事情：
SELECT INTO oResults (SELECT domain_id FROM db.domains WHERE "md5"=oMD5)来提取domain_id。可能会对性能造成一些影响，但“正确但有点慢”比“快但有问题”要好。
最终，您可能会遇到比成功插入更多的异常。然后，您可以尝试在表中插入对
db.domains的引用（通过外键）并在那里捕获 FK 违规。如果您遇到 FK 违规，请在 db.domains 上执行旧的“插入并忽略唯一违规”，然后重试导致 FK 违规的插入。这是相同的基本思想，只是选择哪一个可能会抛出最少的异常并遵循它。You could just go ahead and try to insert the MD5 and catch the error, if you get a "unique constraint violation" error then ignore it and keep going, if you get some other error then bail out. That way you push the duplicate checking right down to the database and your race condition goes away.
Something like this:
SELECT INTO oResults (SELECT domain_id FROM db.domains WHERE "md5"=oMD5)to extract thedomain_id.There might be a bit of a performance hit but "correct and a little slow" is better than "fast but broken".
Eventually you might end up with more exceptions that successful inserts. Then you could try to insert in the table the references (through a foreign key) your
db.domainsand trap the FK violation there. If you had an FK violation, then do the old "insert and ignore unique violations" ondb.domainsand then retry the insert that gave you the FK violation. This is the same basic idea, it just a matter of choosing which one will probably throw the least exceptions and go with that.