serializability and unique constraint violations

From: Jonathan Amsterdam <jba(at)google(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: serializability and unique constraint violations
Date: 2021-02-25 17:50:05
Message-ID: CAOqyxwL4E_JmUScYrnwd0_sOtm3bt4c7G++UiD2PnmdGJFiqyQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Browse pgsql-general by date

  From Date Subject
Next Message Alexander Farber 2021-02-25 18:39:31 Re: Deleting takes days, should I add some index?
Previous Message Rob Sargent 2021-02-25 16:40:42 Re: Postgres Analog of Oracle APPEND hint