如何在多线程环境中安全地向Postgres插入/更新值

Question

我在PostgreSQL中有一张表，它看起来像这样：

 create table item_counts {
  item string,
  view_count int}

我想利用表格来追踪“项”的出现次数，必要时递增计数。初始情况下，表格未填充，因此仅在第一次观察到新值时插入该值，否则将增加“查看次数”。速度和多任务处理都是关注的问题。

我知道可以这样做：

rows_affected = execute("update item_counts set view_count = view_count + 1 
  where item = ?")
if rows_affected == 0:
   execute("insert into item_counts ...")

然而，在多线程环境下，这样做是不安全的，因此我需要将其包装到一个事务中。这反过来会降低速度，因为每次插入/更新后都需要提交。

有没有什么干净高效的建议呢？

- Ash

---

可能是Insert, on duplicate update (postgresql)的重复问题。 - Lukas Eder

恨说，但是即使使用单行DML语句，你已经有了一个“慢”的事务正在进行。让数据库做它的工作，这是它擅长的。同样，我同意wduplicate：这是一个重复的内容。 - anon

Answer 1

如果您使用的是9.1版本，您可能会考虑可写的公共表达式（CTE）：

http://vibhorkumar.wordpress.com/2011/10/26/upsertmerge-using-writable-cte-in-postgresql-9-1/

http://xzilla.net/blog/2011/Mar/Upserting-via-Writeable-CTE.html

- user330315

---

我必须说，那真是非常了不起，同时也有点奇怪和棘手要阅读;-) 因此，在Postgres中可以使用CTE几乎完全模拟SQL:2003标准的MERGE语句... - Lukas Eder

我认为仍然存在竞争条件的可能性，但比那些“upsert()”函数要少。 - user330315

Answer 2

或者，您可以在违反唯一约束时进行检查点、插入和更新（回滚检查点）。是否更好仍然存在疑问，特别是如果您预计大部分都是更新操作。

此外，在并发情况下事务提交可能仍会失败。

还可以使用insertselect的方式，插入表中不存在的数据（使用自身左连接或where not exists子句，任选其一），然后在它返回0行受影响时执行更新操作。

而且，最好将其放在服务器端的一个函数中。

- Michael Krelin - hacker