Postgres无法创建唯一索引，键重复 [英] Postgres could not create unique index, key is duplicated
问题描述
我正在尝试使用此看似简单的SQL在我的Postgres 9.3数据库中的表中添加一列：
I'm trying to add a column to a table in my Postgres 9.3 database with this seemingly simple SQL:
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.
奇怪的是，实际上没有没有行具有该ID（主键，因此它不应重复）：
Strangely enough, there are actually no rows with that id (which is the primary key, so it shouldn't have duplicates):
SELECT id FROM quizzes WHERE id = 10557462;
id
----
(0 rows)
实际上，似乎已以某种方式跳过了该ID：
In fact, it looks like that id has been skipped somehow:
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?
推荐答案
我怀疑您已经存在索引损坏或可见性问题。
I suspect you have pre-existing index corruption or visibility issues.
当您 ALTER TABLE ...添加列... DEFAULT ... 它会进行全表重写。
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.
您可能会发现 VACUUM FULL
You'll probably find that VACUUM FULL on the table produces the same error.
我希望
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;
将显示元组确实存在。
请先阅读此Wiki页面并采取行动。完成此操作后，请检查您的版本。您正在运行还是曾经运行过低于9.3.9的PostgreSQL 9.3版本？特别是作为后来被推广的复制品？如果是这样，那可能是由于已知的多重错误已在此处解决的原因所致：
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.7 relnotes
- 9.3.9 relnotes
否则，很难说发生了什么事。有必要使用 pageinspect 在 pg_controldata 输出中查看问题堆页面，并可能在引用这些堆页面的b树页面上。
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_controldata output, and possibly at the b-tree pages referring to those heap pages.
这篇关于Postgres无法创建唯一索引，键重复的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
