PostgreSQL sometimes fails to detect serialization failures I have an http-server in application language that looks roughly like this:
item = SELECT item FROM table WHERE field = 'value'
if (item) {
  UPDATE another field on item
} else {
  INSERT item VALUE field = 'value' and so on
}

The field has unique constraint. About once every several minutes two requests with the same value come in at the same time. They both execute this same code so of course there is a race condition. I decided that if I wrap it in a transaction with SERIALIZABLE isolation level and retry on every serialization failure then the problem is solved. And on localhost it worked fine. I indeed see serialization failures thrown with error code 40001 as expected.
However on production in a database under load for some reason a Unique constraint violation is sporadically thrown with a code of 23505. It only happens sometimes. I put a lot of effort to  reproduce it on localhost but failed. Everytime I get a normal 40001. I tried to run server code in separate processes and to put delays at different points to enforce certain orders of execution. It only happens on production. So I added extensive logging to prod. The logs say that every time when a problem occurs the item is not found at first but when INSERT happens it triggers Unique constraint violation. So it looks just ad a race condition.
But why sometimes under some conditions Postgres SERIALIZABLE transactions don't detect it as they should? Or how to debug it? It happens like several times in a day out of several dozens of cases.
The (almost) exact SQL look like this:
(I omitted detailed fields lists and exact parameters)
START TRANSACTION
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

SELECT *
FROM products
WHERE product_code = $1
LIMIT 1 -- product_code is a parameter

SELECT *
FROM licenses
WHERE license_key = $1
LIMIT 1 -- license_key is another and it's unique across the table
-- At this point SELECT did not return anything but it was inserted before this INSERT by exactly the same transaction

INSERT INTO licenses (&quot;license_key&quot;, &quot;product_id&quot;, &quot;other_data&quot;,...)
VALUES ($1, $2, $3,...) RETURNING &quot;id&quot;
query failed: error: duplicate key value violates unique constraint &quot;licenses_license_key_key&quot;
Doing this in a serialized transaction should work, unless there is a bug.
As a workaround, use this command, which tries to add a new license; if the license key already exists, it adds the new hours to the existing value:
INSERT INTO licenses(license_key, product_id, hours)
VALUES ($1,
        (SELECT product_id FROM products WHERE product_code = $2 LIMIT 1),
        $3)
ON CONFLICT (license_key) DO UPDATE
SET hours = hours + EXCLUDED.hours
RETURNING id;

documentation