PostgreSQL 在主键上的插入因争用而失败，即使在可序列化级别也是如此

Question

我正在尝试在 PostgreSQL 数据库中插入或更新数据。最简单的情况是键值对(实际数据比较复杂，但这是最小的清晰示例)

当你设置一个值时，如果键不存在，我希望它插入，否则更新。遗憾的是 Postgres 没有插入或更新语句，所以我必须自己模拟它。

我一直在研究基本上SELECT确定 key 是否存在，然后运行适当的INSERT 或UPDATE 的想法。现在很明显，这需要在事务中进行，否则可能会发生各种不好的事情。

但是，这并不是我想要的那样工作 - 我知道可序列化事务存在限制，但我不确定如何解决这个问题。

情况是这样的-

ab: => set transaction isolation level serializable;
a:  => select count(1) from table where id=1; --> 0
b:  => select count(1) from table where id=1; --> 0
a:  => insert into table values(1); --> 1
b:  => insert into table values(1); --> 
    ERROR:  duplicate key value violates unique constraint "serial_test_pkey"

现在我希望它会抛出通常的“由于并发更新而无法提交”，但我猜测因为插入是不同的“行”，所以这不会发生。

有没有简单的方法来解决这个问题？

Answer 1

在 Postgres 9.1 之前存在隔离问题:

asking for serializable isolation guaranteed only that a single MVCC snapshot would be used for the entire transaction, which allowed certain documented anomalies.

也许您遇到了这些“异常”。

你可以试试SELECT … FOR UPDATE在检查行是否存在时。

或者，LOCK TABLE自己。

如果您正在尝试实现 UPSERT，那么稍微更可靠(或者不那么不可靠)的方法是首先尝试 UPDATE，检查受影响的行数，然后如果没有更新行则尝试 INSERT。