触发器有时会失败并出现重复密钥错误

Question

我在AWS中使用PostgreSQL RDS实例。基本上，有一个查询将数据插入到第一个表中，我们称之为table.那里的数据在某些字段中可能有重复项(显然主键除外)。

然后是更新另一个表的触发器，infotable，不允许重复。

触发器：

CREATE TRIGGER insert_infotable AFTER INSERT ON table
FOR EACH ROW EXECUTE PROCEDURE insert_infotable();

触发器函数的相关部分如下所示：

CREATE OR REPLACE FUNCTION insert_infotable() RETURNS trigger AS $insert_infotable$
BEGIN   
--some irrelevant code
IF NOT EXISTS (SELECT * FROM infotable WHERE col1 = NEW.col1 AND col2 = NEW.col2) THEN
INSERT INTO infotable(col1, col2, col3, col4, col5, col6) values (--some values--);
END IF;
RETURN NEW;
END;
$insert_infotable$ LANGUAGE plpgsql;

表infotable对列col1和col2具有 UNIQUE 约束。

一般来说，一切正常，但很少，大约在 1k 次插入中，触发器返回错误"重复键值违反表infotable的唯一约束"unique_col1_and_col2"。这不应该发生，因为触发器函数中有IF NOT EXISTS部分。

第一个问题是这可能是什么原因？我唯一能想到的是两个用户同时获得相同信息的比赛，两个用户都触发触发器，但随后一个用户通过触发器更新第二个表，第二个用户收到重复错误。正因为如此，他的整个插入查询失败了，包括插入到主table。

如果是这种情况，我该怎么办？对于应该有 100+ 用户同时插入数据的表，在插入时使用锁是一个好主意吗？

如果是，我应该使用哪种类型的锁以及我应该锁定的表——主表，还是第二个表，它被触发器修改了？(或者我想我应该在我的主插入语句中或在触发器函数内锁定锁？

Answer 1

是的，这是一个竞争条件。同时运行的两个此类触发器不会看到彼此的修改，因为事务尚未提交。

由于您对infotable有唯一的约束，因此您可以简单地使用

INSERT INTO infotable ...
ON CONFLICT (col1, col2) DO NOTHING;