PostgreSQL inserts an error on the primary key, even at the serializable level

Question

I am trying to insert or update data in PostgreSQL db. The simplest case is pairing key values ​​(actual data is more complicated, but this is the least clear example)

When you set the value, I would like it to be inserted if the key does not exist, otherwise an update. Sadly Postgres has no instructions for inserting or updating, so I have to emulate it myself.

I worked with the idea mainly SELECTabout whether the key exists, and then run the corresponding INSERTor UPDATE. Now it’s clear that it must be in a transaction or all kinds of bad things can happen.

However, this does not work exactly how I like it - I understand that there are restrictions for serializable transactions, but I'm not sure how to get around this problem.

Here is 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 “failed to fix due to simultaneous update”, but I assume that the inserts have different “lines”, this does not happen.

Is there an easy way around this?

Answer 1

Prior to Postgres 9.1, there were isolation issues:

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

, "".

SELECT... FOR UPDATE .

LOCK TABLE .

UPSERT, (, , ) - UPDATE, INSERT, .