15

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.
24

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.

13

As I understand it, your issue here is that the constraint is checked after each statement, but you want it checked at the end of the transaction, so it compares the before-state to the after-state, ignoring the intermediate states.

If so, that is possible with a deferrable constraint.

See SET CONSTRAINTS and DEFERRABLE constraints as documented in CREATE TABLE.

Note that deferred constraints have costs - the system has to keep a list of them to check at commit time, so they're not good for transactions that make huge sets of changes. They are also slower to check.

So I think you probably want to:

ALTER TABLE mytable ALTER CONSTRAINT category_name_key DEFERRABLE;

Note that there appears to be a limitation on ALTER TABLE setting constraints to DEFERRABLE; you might have to instead DROP and re-ADD the constraint.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.