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) — Lukas Eder
– Lukas Eder, Commented Nov 22, 2011 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. — anon
– anon, Commented Nov 23, 2011 at 4:29

user330315 · Accepted Answer · 2011-11-22 14:03:01Z

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, 2011 at 14:03

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

Lukas Eder Over a year ago

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...

user330315 Over a year ago

I think there is still the possibility for a race condition with that, but less likely than the "upsert()" functions floating around I guess

Michael Krelin - hacker · Accepted Answer · 2011-11-22 13:59:03Z

0

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.

answered Nov 22, 2011 at 13:59

Michael Krelin - hacker

144k25 gold badges200 silver badges177 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related