Brisanje kloniranih zapisa iz tabele bez PK

Postoje situacije kada na tabelu bez primarnog ključa ili neki drugi jedinstveni indeks, zbog previda, uključeni su kompletni klonovi već postojećih zapisa.

Na primjer, vrijednosti hronološke metrike se upisuju u PostgreSQL pomoću COPY toka, a onda dolazi do iznenadnog kvara i opet stiže dio potpuno identičnih podataka.

Kako osloboditi bazu podataka nepotrebnih klonova?

Kada PK nije pomoćnik

Najlakši način je spriječiti pojavu takve situacije. Na primjer, roll PRIMARY KEY. Ali to nije uvijek moguće bez povećanja obima pohranjenih podataka.

Na primjer, ako je tačnost izvornog sistema veća od tač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 sa ts sekundom ranije, ali je ostalo prilično valjano sa stanovišta aplikacije (na kraju krajeva, vrijednosti podataka su različite!).

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

Mogu PK(metrički, ts, podaci) - ali to će uvelike povećati njegovu zapreminu, koju nećemo koristiti.

Stoga je najispravnija opcija napraviti običan nejedinstveni indeks (metrički, ts) i rješavati probleme nakon činjenice ako se pojave.

"Klonički rat je počeo"

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

Modelirajmo originalne 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 zadrhtala tri puta, Ctrl+V je zapela, a sad...

Prvo, hajde da shvatimo da naša tabela može biti veoma velika, pa nakon što pronađemo sve klonove, preporučljivo je da bukvalno „bodemo prstom“ da izbrišemo određene zapise bez njihovog ponovnog pretraživanja.

A postoji i takav način - ovaj adresiranje po ctid, fizički identifikator određenog zapisa.

To jest, prije svega, trebamo prikupiti ctid zapisa u kontekstu kompletnog sadržaja reda tabele. Najjednostavnija opcija je prebaciti 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)"}

Da li je moguće ne baciti?U principu, to je moguće u većini slučajeva. Dok ne počnete koristiti polja u ovoj tabeli 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 sve 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 da pišu 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 stringa nije zanimljiva, jednostavno smo je izbacili iz vraćenih stupaca potupita.

Ostalo je još samo malo - natjerati DELETE da 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[]);

Hajde da se proverimo:

[pogledajte objasni.tensor.ru]

Da, sve je ispravno: naša 3 zapisa odabrana su za jedino Seq skeniranje cijele tablice, a čvor Delete je korišten za traženje podataka jedan prolaz sa Tid Scan:

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

Ako ste obrisali mnogo zapisa, ne zaboravite pokrenuti VACUUM ANALYZE.

Provjerimo veću tablicu i veći broj 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 objasni.tensor.ru]

Dakle, metoda djeluje uspješno, ali se mora koristiti s određenim oprezom. Zato što za svaki zapis koji se briše, postoji jedna stranica podataka koja se čita u Tid Scan, a jedna u Delete.

izvor: www.habr.com