Brisanje kloniranih zapisa iz tablice bez PK

Postoje situacije kada u tablicu bez primarnog ključa ili neki drugi jedinstveni indeks, zbog propusta su uključeni potpuni klonovi već postojećih zapisa.

Primjerice, vrijednosti kronološke metrike upisuju se u PostgreSQL pomoću COPY streama, a onda dolazi do iznenadnog kvara i opet dolazi dio potpuno identičnih podataka.

Kako osloboditi bazu podataka od nepotrebnih klonova?

Kad PK nije pomagač

Najlakši način je spriječiti da se takva situacija uopće dogodi. Na primjer, bacite PRIMARNI KLJUČ. Ali to nije uvijek moguće bez povećanja količine pohranjenih podataka.

Na primjer, ako je točnost izvornog sustava veća od točnosti polja u bazi podataka:

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}

Jeste li primijetili? Odbrojavanje umjesto 00:00:02 zabilježeno je u bazi podataka s ts sekundu ranije, ali je ostalo prilično valjano s gledišta aplikacije (uostalom, vrijednosti podataka su različite!).

Naravno da ti to možeš PK(metrički, ts) - ali tada ćemo dobiti sukobe umetanja za važeće podatke.

Mogu učiniti PK(metrika, ts, podaci) - ali to će mu jako povećati volumen, što nećemo koristiti.

Stoga je najispravnija opcija napraviti redoviti nejedinstveni indeks (metrički, ts) i rješavati probleme naknadno ako se pojave.

"Klonski rat je počeo"

Dogodila se neka nesreća i sada moramo uništiti zapise klonova sa stola.

Modelirajmo izvorne podatke:

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

Ovdje nam je ruka tri puta zadrhtala, Ctrl+V zapela, a sad...

Prvo, shvatimo da naša tablica može biti vrlo velika, pa nakon što pronađemo sve klonove, preporučljivo je da doslovno “bockamo prstom” da izbrišemo određene zapise bez njihovog ponovnog pretraživanja.

I postoji takav način - ovaj adresiranje po ctidu, fizički identifikator određenog zapisa.

To jest, prije svega, moramo prikupiti ctid zapisa u kontekstu kompletnog sadržaja retka tablice. Najjednostavnija opcija je pretvoriti cijeli red u tekst:

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

Je li moguće ne baciti?U principu, moguće je u većini slučajeva. Sve dok ne počnete koristiti polja u ovoj tablici tipovi bez operatora jednakosti:

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, odmah vidimo da ako postoji više od jednog unosa u nizu, to su svi klonovi. Ostavimo ih samo:

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)

Za one koji vole pisati kraćeMožete to napisati i ovako:

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

Budući da nam sama vrijednost serijaliziranog niza nije zanimljiva, jednostavno smo je izbacili iz vraćenih stupaca podupita.

Preostalo je još samo malo - neka DELETE koristi set koji smo dobili:

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

Provjerimo sami:

[pogledajte na expand.tensor.ru]

Da, sve je točno: naša 3 zapisa odabrana su za jedini Seq Scan cijele tablice, a čvor Delete korišten je za traženje podataka jedan prolaz s Tid Scanom:

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

Ako ste izbrisali mnogo zapisa, ne zaboravite pokrenuti VACUUM ANALYSE.

Provjerimo veću tablicu i s većim brojem duplikata:

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;

[pogledajte na expand.tensor.ru]

Dakle, metoda djeluje uspješno, ali se mora koristiti s određenim oprezom. Budući da za svaki zapis koji se izbriše, postoji jedna stranica s podacima pročitana u Tid Scanu i jedna u Delete.

Izvor: www.habr.com