Brisanje zapisov klonov iz tabele brez PK

Obstajajo situacije, ko v tabelo brez primarnega ključa ali kakšnega drugega unikatnega indeksa, so zaradi spregleda vključeni popolni kloni že obstoječih zapisov.

Vrednosti kronološke metrike se na primer zapišejo v PostgreSQL z uporabo toka COPY, nato pa pride do nenadne napake in spet pride del popolnoma enakih podatkov.

Kako se znebiti baze podatkov nepotrebnih klonov?

Ko PK ni pomočnik

Najlažji način je preprečiti, da do takšne situacije sploh pride. Na primer, zavrtite PRIMARNI KLJUČ. Vendar to ni vedno mogoče brez povečanja količine shranjenih podatkov.

Na primer, če je natančnost izvornega sistema višja od natančnosti polja v bazi podatkov:

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}

Ste opazili? Odštevanje namesto 00:00:02 je bilo v podatkovni bazi zabeleženo s ts sekundo prej, vendar je ostalo povsem veljavno z vidika aplikacije (navsezadnje so vrednosti podatkov drugačne!).

Seveda zmoreš PK(metrika, ts) - potem pa bomo dobili spore pri vstavljanju veljavnih podatkov.

Lahko storim PK(metrika, ts, podatki) - vendar bo to močno povečalo njegovo prostornino, ki je ne bomo uporabili.

Zato je najbolj pravilna možnost izdelava navadnega needinstvenega indeksa (metrika, ts) in reševanje težav naknadno, če se pojavijo.

"Klonska vojna se je začela"

Zgodila se je nekakšna nesreča in zdaj moramo uničiti zapise klonov iz mize.

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

Tu se nam je roka trikrat zatresla, Ctrl+V se je zataknila, zdaj pa...

Najprej razumejmo, da je naša tabela lahko zelo velika, zato je priporočljivo, da potem, ko najdemo vse klone, dobesedno »pocukamo s prstom«, da izbrišemo določene zapise, ne da bi jih ponovno iskali.

In obstaja takšen način - to naslavljanje s ctid, fizični identifikator določenega zapisa.

To pomeni, da moramo najprej zbrati ctid zapisov v kontekstu celotne vsebine vrstice tabele. Najenostavnejša možnost je preliti celotno vrstico v besedilo:

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

Ali je mogoče ne oddati?Načeloma je v večini primerov mogoče. Dokler ne začnete uporabljati polj v tej tabeli vrste brez operatorja enakosti:

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, takoj vidimo, da če je v nizu več kot en vnos, so to vsi kloni. Pustimo jih:

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 tiste, ki radi pišete krajšeLahko ga zapišete tudi takole:

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

Ker nas sama vrednost serializiranega niza ne zanima, smo jo preprosto vrgli iz vrnjenih stolpcev podpoizvedbe.

Ostalo je le še malo - naj DELETE uporabi prejeti komplet:

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

Preverimo se:

[ogled na expand.tensor.ru]

Da, vse je pravilno: naši 3 zapisi so bili izbrani za edini Seq Scan celotne tabele, vozlišče Delete pa je bilo uporabljeno za iskanje podatkov en prehod s Tid Scan:

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

Če ste počistili veliko zapisov, ne pozabite zagnati VACUUM ANALIZE.

Preverimo večjo tabelo in z večjim številom dvojnikov:

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;

[ogled na expand.tensor.ru]

Torej metoda deluje uspešno, vendar jo je treba uporabljati previdno. Ker je za vsak zapis, ki je izbrisan, ena podatkovna stran prebrana v Tid Scan in ena v Delete.

Vir: www.habr.com