postgresql - 触发器有时会因重复键错误而失败

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 次插入一次，触发器返回错误“重复键值违反唯一约束“unique_col1_and_col2””用于表 infotable。这不应该发生，因为触发函数中有 IF NOT EXISTS 部分。

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

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

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

Answer 1

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

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

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