更新期间唯一索引冲突 [英] unique index violation during update

问题描述

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

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;

我在'pos'上定义了唯一索引：

I define a unique index on 'pos':

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

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

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)

现在我想要将大于2的所有'pos'值减1并得到一个错误（tr是我从德语到英语的翻译）：

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.

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

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)

如何我避免这种情况吗？我了解到已存储的pl / pgsql函数已嵌入事务中，因此不应出现此问题？

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

推荐答案

对唯一索引进行评估

解决该问题的方法是使用唯一的约束推迟，因此在交易结束时进行评估。

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;

这篇关于更新期间唯一索引冲突的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！