unique index violation during update

Question

I have run into a unique index violation in a bigger db. The original problem occurs in a stored pl/pgsql function.

I have simplified everything to show my problem. I can reproduce it in a rather simple table:

CREATE TABLE public.test
(
  id integer NOT NULL DEFAULT nextval('test_id_seq'::regclass),
  pos integer,
  text text,
  CONSTRAINT text_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);

ALTER TABLE public.test
  OWNER TO root;
GRANT ALL ON TABLE public.test TO root;

I define a unique index on 'pos':

CREATE UNIQUE INDEX test_idx_pos
  ON public.test
  USING btree
  (pos);

Before the UPDATE the data in the table looks like this:

testdb=# SELECT * FROM test;
 id | pos |   text   
----+-----+----------
  2 |   1 | testpos1
  3 |   2 | testpos2
  1 |   5 | testpos4
  4 |   4 | testpos3
(4 Zeilen)
tr: (4 rows)

Now I want to decrement all 'pos' values by 1 that are bigger than 2 and get an error (tr are my translations from German to English):

testdb=# UPDATE test SET pos = pos - 1 WHERE pos > 2;
FEHLER:  doppelter Schlüsselwert verletzt Unique-Constraint »test_idx_pos«
tr: ERROR: duplicate key violates unique constraint »test_idx_pos«
DETAIL:  Schlüssel »(pos)=(4)« existiert bereits.
tr: key »(pos)=(4) already exists.

If the UPDATE had run complete the table would look like this and be unique again:

testdb=# SELECT * FROM test;
 id | pos |   text   
----+-----+----------
  2 |   1 | testpos1
  3 |   2 | testpos2
  1 |   4 | testpos4
  4 |   3 | testpos3
(4 Zeilen)
tr: (4 rows)

How can I avoid such situation? I learned that stored pl/pgsql functions are embedded into transactions, so this problem shouldn't appear?

Answer 1

Unique indexes are evaluated per row not per statement (which is e.g. different to Oracle's implementation)

The solution to this problem is to use a unique constraint which can be deferred and thus is evaluated at the end of the transaction.

So instead of the unique index, define a constraint:

alter table test add constraint test_idx_pos unique (pos)
  deferrable initially deferred;