How to safely INSERT / UPDATE a value in Postgres in a multithreaded environment

Question

I have a table in PostgreSQL that looks like this

 create table item_counts {
  item string,
  view_count int}

I would like to use the table to keep track of occurrences of item, incrementing the counts as necessary. Initially the table is unpopulated, so a new value is inserted iff it is observed for the first time, otherwise the view_count is increased. Speed and multitasking are both concerns.

I know I can do

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

However, this is unsafe in a multithreaded environment, so I would have to wrap it into a transaction. This would in turn decrease the speed, since a commit would occur after each insert/update.

Any suggestions how to do it in a clean and efficient way?

possible duplicate of [Insert, on duplicate update (postgresql)](http://stackoverflow.com/questions/1109061/insert-on-duplicate-update-postgresql) — Lukas Eder, Nov 22 '11 at 13:56
Hate to say it, but you already have one of those "slow" transactions going on even with a single row DML statement. Let the database do its job, this is what it excels at. Also, I agree wduplicate: this is a duplicate. — , Nov 23 '11 at 04:29

score 2 · Answer 1 · answered Nov 22 '11 at 14:03

2

If you are on 9.1, you might consider writeable CTEs:

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

answered Nov 22 '11 at 14:03

I must say, that is truly remarkable, while at the same time a bit weird and tricky to read ;-) So the `SQL:2003` standard `MERGE` statement can be almost fully simulated with `CTE`'s in Postgres... – Lukas Eder Nov 22 '11 at 14:30
I think there is still the possibility for a race condition with that, but less likely than the "upsert()" functions floating around I guess – Nov 22 '11 at 14:34

score 0 · Answer 2 · answered Nov 22 '11 at 13:59

Alternatively, you can checkpoint, insert and update on violating unique exception (rolling back the checkpoint). Whether it's better is doubtful, especially if you expect to by mostly-update.

Also the transaction in case of concurrency may still fail at commit.

Also, you can do the insert select, inserting what's NOT in the table (using self-left-join or where not exists clause, whatever pleases you) and then update if it yields 0 affected rows.

And, perhaps, it's best if you do that in a function on the server side.

How to safely INSERT / UPDATE a value in Postgres in a multithreaded environment

2 Answers2