postgresql Postgres 无法创建唯一索引，键重复
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/45315510/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgres could not create unique index, key is duplicated
提问by Rob Johansen
I'm trying to add a column to a table in my Postgres 9.3 database with this seemingly simple SQL:
我正在尝试使用这个看似简单的 SQL 向 Postgres 9.3 数据库中的表添加一列：
ALTER TABLE quizzes ADD COLUMN deleted BOOLEAN NOT NULL DEFAULT false;
However, I'm getting the following error:
但是，我收到以下错误：
ERROR: could not create unique index "quizzes_pkey"
DETAIL: Key (id)=(10557462) is duplicated.
Strangely enough, there are actually no rowswith that id (which is the primary key, so it shouldn't have duplicates):
奇怪的是，实际上没有具有该 id 的行（这是主键，所以它不应该有重复）：
SELECT id FROM quizzes WHERE id = 10557462;
id
----
(0 rows)
In fact, it looks like that id has been skipped somehow:
事实上，这个 id 似乎以某种方式被跳过了：
SELECT id FROM quizzes WHERE id > 10557459 ORDER BY id LIMIT 4;
id
----------
10557460
10557461
10557463
10557464
(4 rows)
Why is this preventing me from adding a column, and how can I fix it?
为什么这会阻止我添加列，我该如何解决？
回答by Craig Ringer
I suspect you have pre-existing index corruption or visibility issues.
我怀疑您有预先存在的索引损坏或可见性问题。
When you ALTER TABLE ... ADD COLUMN ... DEFAULT ...it does a full table rewrite. This rebuilds all indexes, in the process noticing the problem on the heap.
当您ALTER TABLE ... ADD COLUMN ... DEFAULT ...进行全表重写时。这将重建所有索引，在此过程中注意到堆上的问题。
You'll probably find that VACUUM FULLon the table produces the same error.
您可能会发现VACUUM FULL在表上会产生相同的错误。
I expect that
我希望
BEGIN;
SET LOCAL enable_indexscan = off;
SET LOCAL enable_bitmapscan = off;
SET LOCAL enable_indexonlyscan = off;
SELECT ctid,xmin,xmax,id FROM quizzes WHERE id = 10557462;
ROLLBACK;
will reveal that the tuples actually do exist.
将揭示元组确实存在。
Please first read and act on this wiki page. Once you've done that, check your version. Are you running or have you ever run a PostgreSQL 9.3 version older than 9.3.9? Especially as a replica that was then promoted? If so, that likely explains it due to the known multixact bugs that were fixed there:
请先阅读本维基页面并采取行动。完成后，请检查您的版本。您是否正在运行或曾经运行过低于 9.3.9 的 PostgreSQL 9.3 版本？尤其是作为当时推广的复制品？如果是这样，这可能是由于那里修复的已知 multixact 错误所致：
Otherwise, hard to say what's happening. It'd be necessary to take a look at the problem heap page(s) using pageinspect, at pg_controldataoutput, and possibly at the b-tree pages referring to those heap pages.
否则，很难说发生了什么。有必要使用pageinspect, at pg_controldataoutput 以及可能在引用这些堆页面的 b-tree 页面查看问题堆页面。
回答by Rob Johansen
I've accepted @Craig Ringer's answer because I never would have been able to resolve the problem without it. In case it helps anyone else, here's the exact query I used to solve the problem (luckily for me, the duplicates can be deleted):
我接受了@Craig Ringer 的回答，因为没有它我永远无法解决问题。如果它对其他人有帮助，这里是我用来解决问题的确切查询（对我来说幸运的是，可以删除重复项）：
BEGIN;
SET LOCAL enable_indexscan = off;
SET LOCAL enable_bitmapscan = off;
SET LOCAL enable_indexonlyscan = off;
DELETE FROM quizzes WHERE id = 10557462;
COMMIT;
After that, my original query finally succeeded:
之后，我原来的查询终于成功了：
ALTER TABLE quizzes ADD COLUMN deleted BOOLEAN NOT NULL DEFAULT false;
