PostgreSQL Serialisation failure on different ids
The reason is that the table is completely empty.

See the following code in _bt_first in src/backend/access/nbtree/nbtsearch.c:

if (!BufferIsValid(buf))
{
    /*
     * We only get here if the index is completely empty. Lock relation
     * because nothing finer to lock exists.
     */
    PredicateLockRelation(rel, scan-&gt;xs_snapshot);
[...]
    return false;
}
else
    PredicateLockPage(rel, BufferGetBlockNumber(buf),
                      scan-&gt;xs_snapshot);


Normally, the index scan would put an SIRead lock on the index page where the index entry should be, but since the index is empty, PostgreSQL resorts to putting an SIRead lock on the whole table.

Now since both transactions do that, you get a serialization error at the end of one of them, because the writes conflict with the table-wide read lock.

If the table were not empty, you would notice that concurrent transactions like these sometimes succeed, because they affect different index pages. If the affected uids are sufficiently close together that they are on the same index page, you would still experience a &ldquo;false positive&rdquo; serialization error. This is documented:


  While PostgreSQL's Serializable transaction isolation level only allows concurrent transactions to commit if it can prove there is a serial order of execution that would produce the same effect, it doesn't always prevent errors from being raised that would not occur in true serial execution. In particular, it is possible to see unique constraint violations caused by conflicts with overlapping Serializable transactions even after explicitly checking that the key isn't present before attempting to insert it.


To see what kind of SIReadLock PostgreSQL uses, inspect pg_locks.