webentwicklung-frage-antwort-db.com.de

Postgres konnte keinen eindeutigen Index erstellen, der Schlüssel wurde dupliziert

Ich versuche, einer Tabelle in meiner Postgres 9.3-Datenbank eine Spalte mit diesem scheinbar einfachen SQL hinzuzufügen:

ALTER TABLE quizzes ADD COLUMN deleted BOOLEAN NOT NULL DEFAULT false;

Ich erhalte jedoch den folgenden Fehler:

ERROR:  could not create unique index "quizzes_pkey"
DETAIL:  Key (id)=(10557462) is duplicated.

Seltsamerweise gibt es tatsächlich keine Zeilen mit dieser ID (die der Primärschlüssel ist, also keine Duplikate enthalten sollte):

SELECT id FROM quizzes WHERE id = 10557462;
 id 
----
(0 rows)

Tatsächlich sieht es so aus, als ob diese ID irgendwie übersprungen wurde:

SELECT id FROM quizzes WHERE id > 10557459 ORDER BY id LIMIT 4;
    id    
----------
 10557460
 10557461
 10557463
 10557464
(4 rows)

Warum hindert mich das daran, eine Spalte hinzuzufügen, und wie kann ich das beheben?

5
Rob Johansen

Ich vermute, Sie haben vorbestehende Indexkorruptions- oder Sichtbarkeitsprobleme.

Wenn Sie ALTER TABLE ... ADD COLUMN ... DEFAULT ... eingeben, wird eine vollständige Tabellenumschreibung durchgeführt. Dadurch werden alle Indizes neu erstellt, wobei das Problem auf dem Heap erkannt wird.

Sie werden wahrscheinlich feststellen, dass VACUUM FULL in der Tabelle den gleichen Fehler verursacht.

Das erwarte ich

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;

wird zeigen, dass die Tupel tatsächlich existieren.

Bitte lesen und handeln Sie zuerst auf dieser Wiki-Seite . Überprüfen Sie anschließend Ihre Version. Laufen Sie oder haben Sie jemals eine ältere PostgreSQL 9.3-Version als 9.3.9 ausgeführt? Vor allem als Nachbau, der damals beworben wurde? Wenn ja, ist dies wahrscheinlich auf die bekannten Multixact-Fehler zurückzuführen, die dort behoben wurden:

Ansonsten schwer zu sagen, was passiert. Es wäre notwendig, einen Blick auf die problematische (n) Heap-Seite (n) mit pageinspect, auf die Ausgabe von pg_controldata und möglicherweise auf die B-Tree-Seiten zu werfen, die sich auf diese Heap-Seiten beziehen.

6
Craig Ringer

Ich habe die Antwort von @Craig Ringer akzeptiert, weil ich das Problem ohne sie nicht hätte lösen können. Falls es jemand anderem hilft, hier ist die genaue Abfrage, mit der ich das Problem gelöst habe (zum Glück können die Duplikate gelöscht werden):

BEGIN;
SET LOCAL enable_indexscan = off;
SET LOCAL enable_bitmapscan = off;
SET LOCAL enable_indexonlyscan = off; 
DELETE FROM quizzes WHERE id = 10557462;
COMMIT;

Danach war meine ursprüngliche Abfrage endlich erfolgreich:

ALTER TABLE quizzes ADD COLUMN deleted BOOLEAN NOT NULL DEFAULT false;
1
Rob Johansen