Ștergerea înregistrărilor clonate dintr-un tabel fără PK

Sunt situații când la un tabel fără o cheie primară sau un alt index unic, din cauza unei neglijeri, sunt incluse clone complete ale înregistrărilor deja existente.

De exemplu, valorile unei metrici cronologice sunt scrise în PostgreSQL folosind un flux COPY, apoi are loc o eroare bruscă și o parte din datele complet identice sosesc din nou.

Cum să elimini baza de date de clonele inutile?

Când PK nu este un ajutor

Cel mai simplu mod este de a preveni apariția unei astfel de situații în primul rând. De exemplu, rulați CHEIA PRIMARĂ. Dar acest lucru nu este întotdeauna posibil fără creșterea volumului de date stocate.

De exemplu, dacă acuratețea sistemului sursă este mai mare decât acuratețea câmpului din baza de date:

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}

Ai observat? Numărătoarea inversă în loc de 00:00:02 a fost înregistrată în baza de date cu ts cu o secundă mai devreme, dar a rămas destul de valabilă din punct de vedere al aplicației (la urma urmei, valorile datelor sunt diferite!).

Desigur că o poți face PK(metric, ts) - dar apoi vom obține conflicte de inserare pentru date valide.

Pot face PK(metric, ts, data) - dar acest lucru îi va crește foarte mult volumul, pe care nu îl vom folosi.

Prin urmare, cea mai corectă opțiune este de a face un index obișnuit non-unic (metric, ts) și să se ocupe de problemele după fapt, dacă apar.

„Războiul clonic a început”

A avut loc un fel de accident, iar acum trebuie să distrugem înregistrările clonelor de pe masă.

Să modelăm datele originale:

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

Aici mâna noastră a tremurat de trei ori, Ctrl+V s-a blocat, iar acum...

În primul rând, să înțelegem că tabelul nostru poate fi foarte mare, așa că după ce găsim toate clonele, este recomandabil să ne „împingem degetul” pentru a șterge. înregistrări specifice fără a le căuta din nou.

Și există o astfel de modalitate - aceasta adresarea prin ctid, identificatorul fizic al unei anumite înregistrări.

Adică, în primul rând, trebuie să colectăm ctid-ul înregistrărilor în contextul conținutului complet al rândului tabelului. Cea mai simplă opțiune este să aruncați întreaga linie în text:

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)"}

Este posibil să nu aruncați?În principiu, este posibil în majoritatea cazurilor. Până când începeți să utilizați câmpurile din acest tabel tipuri fără operator de egalitate:

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

Da, vedem imediat că dacă există mai multe intrări în matrice, acestea sunt toate clone. Să le lăsăm doar:

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)

Pentru cei cărora le place să scrie mai scurtPuteți scrie și așa:

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

Deoarece valoarea șirului serializat în sine nu este interesantă pentru noi, pur și simplu am aruncat-o din coloanele returnate ale subinterogării.

Mai rămâne puțin de făcut - faceți DELETE să folosească setul primit:

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

Să ne verificăm:

[uita-te pe explic.tensor.ru]

Da, totul este corect: cele 3 înregistrări ale noastre au fost selectate pentru o singură scanare Seq a întregului tabel, iar nodul Delete a fost folosit pentru a căuta date o singură trecere cu Tid Scan:

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

Dacă ați șters o mulțime de înregistrări, nu uitați să rulați VACUUM ANALYSE.

Să verificăm un tabel mai mare și cu un număr mai mare de duplicate:

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;

[uita-te pe explic.tensor.ru]

Deci, metoda funcționează cu succes, dar trebuie folosită cu oarecare precauție. Deoarece pentru fiecare înregistrare care este ștearsă, există o pagină de date citită în Tid Scan și una în Delete.

Sursa: www.habr.com