Unexpected unique constraint violation during update

Question

In table foo with two columns (id and seq), I'd like to add +1 to seq for all records with an arbitrary seq > 4738. The plan is to insert a new record at seq=4739 once all the seq > 4738 records are shifted by +1.

This is the table.

CREATE TABLE foo
(
  id uuid NOT NULL,
  seq integer NOT NULL,
  CONSTRAINT seq_key UNIQUE (seq)
)

CREATE UNIQUE INDEX idx_id
  ON foo
  USING btree
  (id);

CREATE UNIQUE INDEX idx_seq
  ON foo
  USING btree
  (seq);

I try to achieve the +1 shift with the following query. Note, that I use a subquery in an attempt to update the > 4738 records in descending order (i.e. assuming max seq=10000 then the last record is updated first (10000->10001), then second last (seq=10000 doesn't exist at this point, seq=9999 -> seq=10000 (no constraint violation), then 9998 -> 9999, ... to avoid at any one time that a unique constraint violation occurs). However, this assumes sequential execution of the update query which isn't what's seemingly happening.

When running

UPDATE foo SET seq=anon_1.new_seq FROM (
  SELECT foo.id AS id, foo.seq + 1 AS new_seq
  FROM foo
  WHERE foo.seq > 4738 ORDER BY foo.seq DESC
) AS anon_1 WHERE foo.id = anon_1.id

I get the following error.

duplicate key value violates unique constraint "seq_key" DETAIL: Key (seq)=(7334) already exists.

Obviously, that's unexpected (as the constraint was satisfied before the UPDATE). Is there anything I could try to resolve the problem (different index type, only constraint, only index)? I noticed that this error very much depends on the number of updated records. If fewer records are updated, then this problem doesn't seem to occur (this may hint to some parallel execution for when there are too many records to update which may interleave and cause some intermediate state in which the constraint/index is not unique any more). Some thoughts and ideas would be very appreciated.

Answer 1

You need to create a deferrable constraint rather than an index.

CREATE TABLE foo
(
  id uuid NOT NULL primary key
  seq integer NOT NULL,
  CONSTRAINT seq_key UNIQUE (seq)
    deferrable initially immediate --<< this
);

By declaring the constraint as deferrable it will be checked at the end of the UPDATE statement (to be more precise: at the end of the transaction) rather than row-by-row.