Pastrimi i të dhënave të klonit nga një tabelë pa PK

Ka situata kur në një tabelë pa çelës primar ose ndonjë indeks tjetër unik, për shkak të një mbikëqyrjeje, përfshihen klone të plota të të dhënave ekzistuese.

Për shembull, vlerat e një metrike kronologjike shkruhen në PostgreSQL duke përdorur një rrjedhë COPY, dhe më pas ka një dështim të papritur dhe një pjesë e të dhënave plotësisht identike mbërrin përsëri.

Si të shpëtojmë bazën e të dhënave nga klonet e panevojshme?

Kur PK nuk është ndihmës

Mënyra më e lehtë është që në radhë të parë të parandaloni një situatë të tillë. Për shembull, rrotulloni ÇELËSI PRIMARY. Por kjo nuk është gjithmonë e mundur pa rritur vëllimin e të dhënave të ruajtura.

Për shembull, nëse saktësia e sistemit burimor është më e lartë se saktësia e fushës në bazën e të dhënave:

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}

E keni vënë re? Numërimi mbrapsht në vend të 00:00:02 u regjistrua në bazën e të dhënave me ts një sekondë më parë, por mbeti mjaft i vlefshëm nga pikëpamja e aplikimit (në fund të fundit, vlerat e të dhënave janë të ndryshme!).

Sigurisht që mund ta bësh PK (metrik, ts) - por atëherë do të marrim konflikte të futjes për të dhëna të vlefshme.

Mund të bëjë PK (metrik, ts, të dhëna) - por kjo do të rrisë shumë volumin e tij, të cilin ne nuk do ta përdorim.

Prandaj, opsioni më i saktë është të bëni një indeks të rregullt jo unik (metrik, ts) dhe merreni me problemet pas faktit nëse ato lindin.

"Lufta klonike ka filluar"

Ndodhi një lloj aksidenti dhe tani duhet të shkatërrojmë të dhënat e klonit nga tabela.

Le të modelojmë të dhënat origjinale:

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

Këtu na u drodh dora tri herë, Ctrl+V u mbërthye dhe tani...

Së pari, le të kuptojmë se tabela jonë mund të jetë shumë e madhe, kështu që pasi të gjejmë të gjithë klonet, këshillohet që fjalë për fjalë të "thyejmë gishtin" për ta fshirë. regjistrime specifike pa i rikërkuar ato.

Dhe ka një mënyrë të tillë - kjo duke iu drejtuar nga ctid, identifikuesi fizik i një regjistrimi specifik.

Kjo do të thotë, para së gjithash, ne duhet të mbledhim ctid-in e të dhënave në kontekstin e përmbajtjes së plotë të rreshtit të tabelës. Mundësia më e thjeshtë është të hedhësh të gjithë rreshtin në 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)"}

A është e mundur të mos hedhim?Në parim, është e mundur në shumicën e rasteve. Derisa të filloni të përdorni fushat në këtë tabelë llojet pa operator barazi:

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

Po, ne shohim menjëherë se nëse ka më shumë se një hyrje në grup, të gjitha këto janë klone. Le t'i lëmë ato:

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)

Për ata që duan të shkruajnë më shkurtMund ta shkruani edhe kështu:

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

Meqenëse vlera e vargut të serializuar në vetvete nuk është interesante për ne, ne thjesht e hodhëm atë nga kolonat e kthyera të nënpyetjes.

Mbetet vetëm pak për të bërë - bëje DELETE të përdorë grupin që morëm:

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

Le të kontrollojmë veten:

[shikoni në shpjegojnë.tensor.ru]

Po, gjithçka është e saktë: 3 regjistrimet tona u zgjodhën për të vetmin Seq Scan të të gjithë tabelës dhe nyja Delete u përdor për të kërkuar të dhëna kalim i vetëm me Tid Scan:

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

Nëse keni pastruar shumë të dhëna, mos harroni të ekzekutoni VACUUM ANALYZE.

Le të kontrollojmë për një tabelë më të madhe dhe me një numër më të madh kopjesh:

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;

[shikoni në shpjegojnë.tensor.ru]

Pra, metoda funksionon me sukses, por duhet përdorur me pak kujdes. Sepse për çdo regjistrim që fshihet, ka një faqe të dhënash të lexuar në Tid Scan dhe një në Delete.

Burimi: www.habr.com