Avoiding Unique Violation in Atomic Transactions in PostgreSQL

postgresqltransactionunique-constraint

Is possible to create atomic transaction in PostgreSQL?

Consider I have table category with these rows:

id|name
--|---------
1 |'tablets'
2 |'phones'

And column name has unique constraint.

If I try:

BEGIN;
update "category" set name = 'phones' where id = 1;
update "category" set name = 'tablets' where id = 2;
COMMIT;

I'm getting:

ERROR:  duplicate key value violates unique constraint "category_name_key"
DETAIL:  Key (name)=(tablets) already exists.

Best Answer

In addition to what @Craig provided (and correcting some of it):

Effective Postgres 9.4, UNIQUE, PRIMARY KEY and EXCLUDE constraints are checked immediately after each row when defined NOT DEFERRABLE. This is different from other kinds of NOT DEFERRABLE constraints (currently only REFERENCES (foreign key)) which are checked after each statement. We worked all of this out under this related question on SO:

It is not enough for a UNIQUE (or PRIMARY KEY or EXCLUDE) constraint to be DEFERRABLE to make your presented code with multiple statements work.

And you can not use ALTER TABLE ... ALTER CONSTRAINT for this purpose. Per documentation:

ALTER CONSTRAINT

This form alters the attributes of a constraint that was previously created. Currently only foreign key constraints may be altered.

Bold emphasis mine. Use instead:

ALTER TABLE t
   DROP CONSTRAINT category_name_key
 , ADD  CONSTRAINT category_name_key UNIQUE(name) DEFERRABLE;

Drop and add the constraint back in a single statement so there is no time window for anybody to sneak in offending rows. For big tables it would be tempting to conserve the underlying unique index somehow, because it is costly to delete and recreate it. Alas, that does not seem to be possible with standard tools (if you have a solution for that, please let us know!):

For a single statement making the constraint deferrable is enough:

UPDATE category c
SET    name = c_old.name
FROM   category c_old
WHERE  c.id     IN (1,2)
AND    c_old.id IN (1,2)
AND    c.id <> c_old.id;

A query with CTEs also is a single statement:

WITH x AS (
    UPDATE category SET name = 'phones' WHERE id = 1
    )
UPDATE category SET name = 'tablets' WHERE id = 2;

However, for your code with multiple statements you (additionally) need to actually defer the constraint - or define it as INITIALLY DEFERRED Either is typically more expensive than the above. But it may not be easily feasible to pack everything into one statement.

BEGIN;
SET CONSTRAINTS category_name_key DEFERRED;
UPDATE category SET name = 'phones'  WHERE id = 1;
UPDATE category SET name = 'tablets' WHERE id = 2;
COMMIT;

Be aware of a limitation in connection with FOREIGN KEY constraints, though. Per documentation:

The referenced columns must be the columns of a non-deferrable unique or primary key constraint in the referenced table.

So you cannot have both at the same time.

Related Question