postgresql - 更新期间违反唯一索引

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);

在 UPDATE 之前，表中的数据如下所示:

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.

如果 UPDATE 运行完成，表将看起来像这样并且再次是唯一的:

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;