Klono įrašų išvalymas iš lentelės be PK

Būna situacijų, kai į lentelę be pirminio rakto arba koks nors kitas unikalus indeksas, dėl priežiūros įtraukiami visi jau esamų įrašų klonai.

Pavyzdžiui, chronologinės metrikos reikšmės įrašomos į PostgreSQL naudojant COPY srautą, tada staiga įvyksta gedimas ir vėl atsiranda dalis visiškai identiškų duomenų.

Kaip pašalinti nereikalingų klonų duomenų bazę?

Kai PK nėra pagalbininkas

Lengviausias būdas yra užkirsti kelią tokiai situacijai. Pavyzdžiui, pasukite PRIMARY KEY. Tačiau tai ne visada įmanoma nepadidinus saugomų duomenų apimties.

Pavyzdžiui, jei šaltinio sistemos tikslumas yra didesnis nei duomenų bazės lauko tikslumas:

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}

Ar tu pastebėjai? Atgalinis skaičiavimas vietoj 00:00:02 buvo įrašytas į duomenų bazę su ts sekunde anksčiau, tačiau išliko gana tinkamas taikymo požiūriu (juk duomenų reikšmės skiriasi!).

Žinoma, galite tai padaryti PK(metrika, ts) - bet tada gausime galiojančių duomenų įterpimo konfliktus.

Gali padaryti PK(metrika, ts, duomenys) - bet tai labai padidins jo apimtį, kurios mes nenaudosime.

Todėl teisingiausias variantas yra padaryti įprastą neunikalią indeksą (metrika, ts) ir spręsti problemas po fakto, jei jos iškyla.

„Prasidėjo kloninis karas“

Atsitiko kažkokia nelaimė, ir dabar turime sunaikinti klonų įrašus nuo lentelės.

Modeliuokime pradinius duomenis:

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

Čia mūsų ranka tris kartus drebėjo, Ctrl+V užstrigo, o dabar...

Pirma, supraskime, kad mūsų lentelė gali būti labai didelė, todėl radus visus klonus patartina tiesiogine prasme „bakstyti pirštą“, kad ištrintume konkrečius įrašus iš naujo jų neieškodami.

Ir yra toks būdas – tai kreipiantis per ctd, fizinis konkretaus įrašo identifikatorius.

Tai yra, visų pirma, turime surinkti įrašų ctid viso lentelės eilutės turinio kontekste. Paprasčiausias variantas yra įterpti visą eilutę į 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)"}

Ar galima nelieti?Iš esmės tai įmanoma daugeliu atvejų. Kol nepradėsite naudoti šios lentelės laukų tipai be lygybės operatoriaus:

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

Taip, iš karto matome, kad jei masyve yra daugiau nei vienas įrašas, tai visi klonai. Tiesiog palikime juos:

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)

Mėgstantiems rašyti trumpiauTaip pat galite parašyti taip:

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

Kadangi pati serializuotos eilutės reikšmė mums nėra įdomi, mes ją tiesiog išmetėme iš grąžinamų antrinės užklausos stulpelių.

Liko šiek tiek padaryti – priverskite DELETE naudoti gautą rinkinį:

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

Pasitikrinkime patys:

[pažiūrėkite paaiškinkite.tensor.ru]

Taip, viskas teisinga: mūsų 3 įrašai buvo pasirinkti vieninteliam visos lentelės „Seq Scan“, o mazgas „Delete“ buvo naudojamas duomenų paieškai. vienas leidimas su „Tid Scan“.:

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

Jei išvalėte daug įrašų, nepamirškite paleisti VACUUM ANALIZĖS.

Patikrinkime didesnę lentelę ir didesnį dublikatų skaičių:

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;

[pažiūrėkite paaiškinkite.tensor.ru]

Taigi, metodas veikia sėkmingai, tačiau jį reikia naudoti atsargiai. Nes kiekvienam ištrintam įrašui Tid Scan nuskaitomas vienas duomenų puslapis, o Delete – vienas.

Šaltinis: www.habr.com