16

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.

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)

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?

Improve this question
2
  • 1
    I don't see how adding the deleted column could result in a unique key violation. Are you sure that is the code that is running? Commented Jul 26, 2017 at 0:31
  • 1
    Yes, I'm sure. I'm typing that code at a psql prompt and pressing Enter. Commented Jul 26, 2017 at 0:32

3 Answers 3

Reset to default
17

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.

You'll probably find that VACUUM FULL on the table produces the same error.

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:

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thanks for your response. I ran the queries you suggested, and there are indeed 5 rows with id 10557462. And according to SELECT id, count(*) FROM quizzes GROUP BY id HAVING count(*) > 1;, this is the only quiz with duplicates. Any other advice in addition to acting on the data corruption wiki page?
This resolves the issue for me. It forced the hidden keys and indexes out to be displayed. Just change the column names to any column on your table, then refresh the table and check the keys and indexes you will fine them there, then delete the duplicates and all will be good.
10

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

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;
Improve this answer

1 Comment

Personally, I strongly advise that you now pg_dump the db(s), pg_dumpall --globals-only, then initdb a replacement blank postgres instance and restore into it. I wouldn't trust that there are no other undetected problems with this one.
0

For me, I was updating columns from text to citext. I had an index on the text field containing matching values of different case sensitivity. When I tried changing the text column to citext, the index was then duplicated (because case no longer mattered) and threw an error.

The solution was to make sure there's no case insensitive duplicates in that field.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.