Klona ierakstu dzēšana no tabulas bez PK

Ir situācijas, kad uz tabulu bez primārās atslēgas vai kāds cits unikāls indekss, pārraudzības dēļ tiek iekļauti jau esošu ierakstu pilni kloni.

Piemēram, hronoloģiskās metrikas vērtības tiek ierakstītas PostgreSQL, izmantojot COPY straumi, un tad notiek pēkšņa kļūme, un daļa no pilnīgi identiskiem datiem tiek saņemta vēlreiz.

Kā atbrīvot datubāzi no nevajadzīgiem kloniem?

Kad PK nav palīgs

Vienkāršākais veids ir novērst šādas situācijas rašanos. Piemēram, ritiniet PRIMARY KEY. Bet tas ne vienmēr ir iespējams, nepalielinot saglabāto datu apjomu.

Piemēram, ja avota sistēmas precizitāte ir augstāka par datu bāzes lauka precizitāti:

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}

Vai pamanījāt? Atpakaļskaitīšana 00:00:02 vietā tika reģistrēta datu bāzē ar ts sekundi agrāk, taču no pielietojuma viedokļa palika diezgan derīga (galu galā datu vērtības ir atšķirīgas!).

Protams, jūs varat to izdarīt PK(metrika, ts) - bet tad mēs saņemsim ievietošanas konfliktus derīgiem datiem.

Var darīt PK(metrika, ts, dati) - bet tas ievērojami palielinās tā apjomu, ko mēs neizmantosim.

Tāpēc pareizākais variants ir izveidot regulāru neunikālu indeksu (metriska, ts) un risināt problēmas pēc fakta, ja tās rodas.

"Kloniskais karš ir sācies"

Notika kaut kāda nelaime, un tagad mums ir jāiznīcina klonu ieraksti no tabulas.

Modelēsim sākotnējos datus:

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

Te mums trīs reizes trīcēja roka, iestrēga Ctrl+V, un tagad...

Pirmkārt, sapratīsim, ka mūsu tabula var būt ļoti liela, tāpēc pēc tam, kad esam atraduši visus klonus, mums ir ieteicams burtiski “iebakstīt ar pirkstu”, lai izdzēstu konkrētus ierakstus, tos atkārtoti nemeklējot.

Un ir tāds veids - šis adresēšana ar ctid, konkrēta ieraksta fiziskais identifikators.

Tas nozīmē, ka, pirmkārt, mums ir jāapkopo ierakstu ctid tabulas rindas pilnā satura kontekstā. Vienkāršākā iespēja ir visu rindu ievietot 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)"}

Vai ir iespējams nemest?Principā vairumā gadījumu tas ir iespējams. Līdz brīdim, kad sākat lietot laukus šajā tabulā veidi bez vienlīdzības operatora:

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

Jā, mēs uzreiz redzam, ka, ja masīvā ir vairāk nekā viens ieraksts, tie visi ir kloni. Atstāsim tos:

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)

Tiem, kam patīk rakstīt īsākVarat arī uzrakstīt šādi:

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

Tā kā serializētās virknes vērtība mums nav interesanta, mēs to vienkārši izmetām no apakšvaicājuma atgrieztajām kolonnām.

Palicis tikai nedaudz darāmā — lieciet DELETE izmantot saņemto komplektu:

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

Pārbaudīsim paši:

[apskatiet skaidro.tensor.ru]

Jā, viss ir pareizi: mūsu 3 ieraksti tika atlasīti vienai visas tabulas secības skenēšanai, un datu meklēšanai tika izmantots mezgls Dzēst. viena caurlaide ar Tid Scan:

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

Ja esat notīrījis daudz ierakstu, neaizmirstiet palaist VAKUUMA ANALĪZI.

Pārbaudīsim lielāku tabulu un lielāku dublikātu skaitu:

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;

[apskatiet skaidro.tensor.ru]

Tātad metode darbojas veiksmīgi, taču tā ir jāizmanto ar zināmu piesardzību. Tā kā katram ierakstam, kas tiek dzēsts, Tid Scan tiek nolasīta viena datu lapa, bet programmā Dzēst - viena.

Avots: www.habr.com