PostgreSQL insert on primary key failing with contention, even at serializable level
- by Steven Schlansker
I'm trying to insert or update data in a PostgreSQL db. The simplest case is a key-value pairing (the actual data is more complicated, but this is the smallest clear example)
When you set a value, I'd like it to insert if the key is not there, otherwise update. Sadly Postgres does not have an insert or update statement, so I have to emulate it myself.
I've been working with the idea of basically SELECTing whether the key exists, and then running the appropriate INSERT or UPDATE. Now clearly this needs to be be in a transaction or all manner of bad things could happen.
However, this is not working exactly how I'd like it to - I understand that there are limitations to serializable transactions, but I'm not sure how to work around this one.
Here's the situation -
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"
Now I would expect it to throw the usual "couldn't commit due to concurrent update" but I'm guessing since the inserts are different "rows" this does not happen.
Is there an easy way to work around this?
