serializability and unique constraint violations

[Jonathan Amsterdam <jba@xxxxxxxxxx>]

I found that I can get a "duplicate key value violates unique constraint" error under certain conditions which I don't think the documentation describes. I don't know if this is a documentation bug, a product bug, or if I'm holding something wrong.

My table is created with

 CREATE TABLE  paths (                                                                                                                                     id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,                                                   path TEXT NOT NULL                                                                                                          );                                                                                                                                                ALTER TABLE paths ADD CONSTRAINT paths_path_key UNIQUE (path);  

CREATE INDEX idx_paths_path_id ON paths(path, id);

I create a serializable transaction that first selects a path from the table, and then inserts it only if it is missing. When I run several of these transactions concurrently, I see the constraint violation error. (Complete Go program at https://gist.github.com/jba/87f95951103aba67794eea04ba307b8c.)

The docs (https://www.postgresql.org/docs/13/transaction-iso.html) are clear that unique constraint violations can violate serializability: "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". However, the next sentence is: "This can be avoided by making sure that all Serializable transactions that insert potentially conflicting keys explicitly check if they can do so first," which I do.

When I remove the index, everything works as documented.