UNIQUE
Constraints in PostgreSQL are not TransactionalI’ve recently encountered an unexpected “problem” with UNIQUE
constraints in PostgreSQL. I’m stunned that I never noticed this before, since I’ve been working with PostgreSQL for many years. Either way, I guess I’m not the only one who doesn’t know about it. So, here it is …
Imagine you have the following table in your database:
test=# \d employees
Table "public.employees"
Column | Type | Modifiers
------------+---------+-------------------------------------
id | uuid | not null default uuid_generate_v4()
ordinality | integer | not null
Indexes:
"employees_pkey" PRIMARY KEY, btree (id)
"employees_ordinality_key" UNIQUE CONSTRAINT, btree (ordinality)
test=# SELECT * FROM employees;
id | ordinality
--------------------------------------+------------
dbcecae2-a879-4f8b-8ba9-79a6821f013e | 1
e2162ccf-d81c-4008-a46f-48640f3f9428 | 2
(2 rows)
Obviously, the UNIQUE
constraint for the ordinality
column is satisfied right now. Things start getting interesting when you attempt to update multiple rows within a single transaction:
BEGIN;
UPDATE employees SET ordinality = ordinality + 1;
COMMIT;
-- expected data after transaction:
-- two rows with ordinalities of 2 and 3.
After committing the transaction, the UNIQUE
constraint should still be satisfied, right? Wrong! On my system, the UPDATE
statement fails with the following error.
ERROR: duplicate key value violates unique constraint "employees_ordinality_key"
DETAIL: Key (ordinality)=(2) already exists.
PostgreSQL updates the table row by row and checks the UNIQUE
constraint after changing each row. I would have expected the check to occur only once when committing the transaction, but that’s not how it works.
To achieve the intent of our transaction (i.e., incrementing oridinality
by one for all rows), you have to rewrite it in a way that avoids collisions at all times. For instance, you could use two UPDATE
statements:
BEGIN;
UPDATE employees SET ordinality = -ordinality; -- negate
UPDATE employees SET ordinality = -ordinality + 1; -- negate and increment
COMMIT;
Ta-dah, problem solved!
Notes:
Software: PostgreSQL 9.5.3