DBA: Löschen von Klonsätzen aus einer Tabelle ohne PK

Es gibt Situationen, in denen in einer Tabelle ohne Primärschlüssel oder einen anderen eindeutigen Index versehentlich vollständige Klone bereits vorhandener Datensätze gelöscht werden.



Zum Beispiel werden die Werte der chronologischen Metrik in den PostgreSQL COPY-Stream geschrieben, und dann tritt ein plötzlicher Fehler auf, und ein Teil der vollständig identischen Daten kommt wieder.

Wie entferne ich unnötige Klone aus der Datenbank?

Wenn PK kein Assistent ist

Der einfachste Weg ist, dies zu verhindern. Zum Beispiel würfeln Sie den gleichen PRIMARY KEY. Dies ist jedoch nicht immer möglich, ohne die Menge der gespeicherten Daten zu erhöhen.

Wenn beispielsweise die Genauigkeit des ursprünglichen Systems höher ist als die Genauigkeit des Felds in der Datenbank:

metric | ts | data -------------------------------------------------- cpu.busy | 2019-12-20 00:00:00 | {"value" : 12.34} cpu.busy | 2019-12-20 00:00:01 | {"value" : 10} cpu.busy | 2019-12-20 00:00:01 | {"value" : 11.2} cpu.busy | 2019-12-20 00:00:03 | {"value" : 15.7} 

Hast du es bemerkt? Die Zählung anstelle von 00:00:02 wurde mit ts eine Sekunde früher in die Datenbank geschrieben, blieb aber aus Sicht der Anwendung durchaus gültig (schließlich sind die Datenwerte unterschiedlich!).

Natürlich können Sie PK (metric, ts) erstellen - aber dann erhalten wir Einfügungskonflikte für gültige Daten.

Sie können PK (metrisch, ts, Daten) erstellen - dies erhöht jedoch das Volumen erheblich, was wir nicht verwenden werden.

Daher ist es am besten, einen regulären, nicht eindeutigen Index (Metrik, ts) zu erstellen und Probleme nachträglich zu behandeln, falls sie auftreten.

"Der klonische Krieg hat begonnen"

Es ist ein Unfall passiert, und jetzt müssen wir die Klonaufzeichnungen vom Tisch löschen.



Lassen Sie uns die Quelldaten simulieren:

 CREATE TABLE tbl(k text, v integer); INSERT INTO tbl VALUES ('a', 1) , ('a', 3) , ('b', 2) , ('b', 2) -- oops! , ('c', 3) , ('c', 3) -- oops!! , ('c', 3) -- oops!! , ('d', 4) , ('e', 5) ; 

Dann zitterte unsere Hand dreimal, Strg + V steckte fest und jetzt ...

Lassen Sie uns zunächst verstehen, dass unsere Tabelle sehr groß sein kann. Nachdem wir alle Klone gefunden haben, ist es für uns ratsam, buchstäblich auf den Finger zu drücken, um bestimmte Datensätze zu löschen , ohne sie erneut durchsuchen zu müssen .

Und es gibt so etwas - das ist die Adressierung mit ctid , der physischen Kennung eines bestimmten Datensatzes.

Das heißt, wir müssen zunächst ctid-Datensätze im Kontext des vollständigen Inhalts der Tabellenzeile sammeln. Am einfachsten ist es, die gesamte Zeile in Text umzuwandeln:

 SELECT T::text , array_agg(ctid) ctids FROM tbl T GROUP BY 1; 

 t | ctids --------------------------------- (e,5) | {"(0,9)"} (d,4) | {"(0,8)"} (c,3) | {"(0,5)","(0,6)","(0,7)"} (b,2) | {"(0,3)","(0,4)"} (a,3) | {"(0,2)"} (a,1) | {"(0,1)"} 

Ja, wir sehen sofort, dass es nur Klone gibt, wenn das Array mehr als einen Datensatz enthält. Lassen wir nur sie:

 SELECT unnest(ctids[2:]) FROM ( SELECT array_agg(ctid) ctids FROM tbl T GROUP BY T::text ) T; 

 unnest ------ (0,6) (0,7) (0,4) 

Da der Wert der serialisierten Zeichenfolge selbst für uns nicht interessant ist, haben wir ihn einfach aus den zurückgegebenen Spalten der Unterabfrage geworfen.

Alles, was übrig bleibt, ist, DELETE zu veranlassen, das erhaltene Set zu verwenden:

 DELETE FROM tbl WHERE ctid = ANY(ARRAY( SELECT unnest(ctids[2:]) FROM ( SELECT array_agg(ctid) ctids FROM tbl T GROUP BY T::text ) T )::tid[]); 

Überprüfe dich selbst:


[siehe EXPLAIN.TENSOR.RU]

Ja, das ist richtig: Unsere 3 Datensätze wurden für den einzigen Seq-Scan der gesamten Tabelle ausgewählt, und der Delete-Knoten suchte mithilfe von Tid Scan in einem Durchgang nach Daten:

 -> Tid Scan on tbl (actual time=0.050..0.051 rows=3 loops=1) TID Cond: (ctid = ANY ($0)) 

Wenn Sie viele Aufzeichnungen gelöscht haben, vergessen Sie nicht, VACUUM ANALYZE zu fahren .

Suchen wir nach einem größeren Tisch mit vielen Einstellungen:

 TRUNCATE TABLE tbl; INSERT INTO tbl SELECT chr(ascii('a'::text) + (random() * 26)::integer) k -- a..z , (random() * 100)::integer v -- 0..99 FROM generate_series(1, 10000) i; 

[siehe EXPLAIN.TENSOR.RU]

Die Methode funktioniert also erfolgreich, muss jedoch mit einiger Vorsicht angewendet werden. Denn für jeden gelöschten Datensatz gibt es einen Lesevorgang der Datenseite in Tid Scan und einen Lesevorgang in Delete.