更新期间的唯一索引冲突

Question

我在一个更大的数据库中遇到了一个唯一的索引冲突。最初的问题发生在存储的pl/pgsql函数中。

为了表明我的问题，我简化了所有的事情。我可以在一个相当简单的桌子上复制它：

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;

我为‘pos’定义了一个唯一的索引：

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

在更新之前，表中的数据如下所示：

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

现在，我希望将大于2的所有'pos‘值减少1，并得到一个错误(tr是我从德语到英语的翻译)：

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.

如果更新已经运行完成，表将类似于此，并且再次是唯一的：

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

我怎样才能避免这种情况呢？我了解到存储的pl/pgsql函数嵌入到事务中，因此不应该出现这个问题？

Answer 1

唯一索引按行计算，而不是每条语句(例如，与Oracle的实现不同)

该问题的解决方案是使用唯一的约束，该约束可以延迟，因此在事务结束时进行评估。

因此，而不是唯一索引的定义了一个约束：

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