Klondatensätze aus einer Tabelle ohne PK löschen

Es gibt Situationen, in denen zu einer Tabelle ohne Primärschlüssel oder ein anderer eindeutiger Index, aufgrund eines Versehens werden vollständige Klone bereits vorhandener Datensätze einbezogen.

Beispielsweise werden die Werte einer chronologischen Metrik mithilfe eines COPY-Streams in PostgreSQL geschrieben, und dann kommt es zu einem plötzlichen Fehler und ein Teil der völlig identischen Daten kommt erneut an.

Wie befreie ich die Datenbank von unnötigen Klonen?

Wenn PK kein Helfer ist

Der einfachste Weg besteht darin, eine solche Situation von vornherein zu verhindern. Rollen Sie beispielsweise PRIMARY KEY. Dies ist jedoch nicht immer möglich, ohne die Menge der gespeicherten Daten zu erhöhen.

Wenn beispielsweise die Genauigkeit des Quellsystems 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 bemerkt? Der Countdown statt 00:00:02 wurde mit ts eine Sekunde früher in der Datenbank erfasst, blieb aber anwendungstechnisch durchaus gültig (die Datenwerte sind schließlich unterschiedlich!).

Natürlich können Sie es tun PK(metrisch, ts) - aber dann kommt es zu Einfügungskonflikten für gültige Daten.

Kann machen PK(metrisch, ts, Daten) - aber dadurch erhöht sich die Lautstärke erheblich, was wir nicht nutzen werden.

Daher besteht die korrekteste Option darin, einen regulären, nicht eindeutigen Index zu erstellen (metrisch, ts) und sich mit Problemen im Nachhinein befassen, wenn sie auftreten.

„Der klonische Krieg hat begonnen“

Es ist ein Unfall passiert, und jetzt müssen wir die Klondatensätze aus der Tabelle vernichten.

Lassen Sie uns die Originaldaten modellieren:

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

Hier zitterte unsere Hand dreimal, Strg+V blieb hängen und jetzt...

Lassen Sie uns zunächst verstehen, dass unsere Tabelle sehr groß sein kann. Nachdem wir alle Klone gefunden haben, ist es ratsam, sie buchstäblich mit dem Finger zu löschen Sie können bestimmte Datensätze aufrufen, ohne sie erneut durchsuchen zu müssen.

Und es gibt so einen Weg – diesen Adressierung durch ctid, die physische Kennung eines bestimmten Datensatzes.

Das heißt, wir müssen zunächst die ctid der Datensätze im Kontext des gesamten Inhalts der Tabellenzeile erfassen. Die einfachste Möglichkeit besteht darin, 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)"}

Ist es möglich, nicht zu casten?Grundsätzlich ist es in den meisten Fällen möglich. Bis Sie anfangen, Felder in dieser Tabelle zu verwenden Typen ohne Gleichheitsoperator:

CREATE TABLE tbl(k text, v integer, x point);
SELECT
  array_agg(ctid) ctids
FROM
  tbl T
GROUP BY
  T;
-- ERROR:  could not identify an equality operator for type tbl

Ja, wir sehen sofort, dass es sich bei mehr als einem Eintrag im Array um Klone handelt. Lassen wir sie einfach stehen:

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)

Für diejenigen, die gerne kürzer schreibenDu kannst es auch so schreiben:

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

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

Es bleibt nur noch ein wenig zu tun: Lassen Sie DELETE den Satz verwenden, den wir erhalten haben:

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[]);

Lassen Sie uns selbst überprüfen:

[siehe EXPLAIN.tensor.ru]

Ja, alles ist richtig: Unsere 3 Datensätze wurden für den einzigen Seq Scan der gesamten Tabelle ausgewählt und der Löschknoten wurde zur Suche nach Daten verwendet Einzeldurchgang mit Tid Scan:

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

Wenn Sie viele Datensätze gelöscht haben, Vergessen Sie nicht, VACUUM ANALYZE auszuführen.

Suchen wir nach einer größeren Tabelle mit einer größeren Anzahl an Duplikaten:

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 wird eine Datenseite in Tid Scan und eine in Delete gelesen.

Source: habr.com