Kloonitietueiden tyhjentäminen taulukosta ilman PK:ta

On tilanteita, jolloin taulukkoon ilman ensisijaista avainta tai jokin muu ainutlaatuinen indeksi, valvonnan vuoksi, jo olemassa olevien tietueiden täydelliset kloonit sisällytetään.

Esimerkiksi kronologisen metriikan arvot kirjoitetaan PostgreSQL:iin COPY-virran avulla, ja sitten tapahtuu äkillinen vika, ja osa täysin identtisistä tiedoista tulee uudelleen.

Kuinka poistaa tietokannasta tarpeettomia klooneja?

Kun PK ei ole auttaja

Helpoin tapa on estää tällaista tilannetta jo alunperin. Esimerkiksi rullaa PRIMARY KEY. Mutta tämä ei aina ole mahdollista lisäämättä tallennetun tiedon määrää.

Jos esimerkiksi lähdejärjestelmän tarkkuus on suurempi kuin tietokannan kentän tarkkuus:

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}

Huomasitko? Lähtölaskenta 00:00:02 sijasta kirjattiin tietokantaan ts:llä sekuntia aikaisemmin, mutta pysyi sovelluksen näkökulmasta varsin voimassa (tietoarvot ovat loppujen lopuksi erilaisia!).

Tietysti voit tehdä sen PK(metri, ts) - mutta sitten saamme lisäysristiriidat kelvollisille tiedoille.

Voi tehdä PK(metri, ts, data) - mutta tämä lisää huomattavasti sen määrää, jota emme käytä.

Siksi oikea vaihtoehto on tehdä tavallinen ei-ainutlaatuinen indeksi (metri, ts) ja käsitellä ongelmia jälkikäteen, jos niitä ilmenee.

"Kloonisota on alkanut"

Tapahtui jonkinlainen onnettomuus, ja nyt meidän on tuhottava kloonitietueet pöydästä.

Mallitetaan alkuperäiset tiedot:

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

Tässä kätemme tärisi kolme kertaa, Ctrl+V jäi jumiin ja nyt...

Ymmärrämme ensin, että taulukkomme voi olla hyvin suuri, joten kun olemme löytäneet kaikki kloonit, meidän on suositeltavaa kirjaimellisesti "työntää sormeamme" poistaaksesi tiettyjä tietueita etsimättä niitä uudelleen.

Ja on olemassa sellainen tapa - tämä osoite ctidillä, tietyn tietueen fyysinen tunniste.

Eli ensinnäkin meidän on kerättävä tietueiden ctid taulukon rivin täydellisen sisällön yhteydessä. Yksinkertaisin vaihtoehto on heittää koko rivi tekstiksi:

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

Onko mahdollista olla heittämättä?Periaatteessa se on mahdollista useimmissa tapauksissa. Kunnes alat käyttää tämän taulukon kenttiä tyypit ilman tasa-arvooperaattoria:

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

Kyllä, näemme heti, että jos taulukossa on useampi kuin yksi merkintä, nämä ovat kaikki klooneja. Jätetään ne vain:

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)

Niille, jotka haluavat kirjoittaa lyhyemminVoit kirjoittaa sen myös näin:

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

Koska itse sarjamuotoisen merkkijonon arvo ei kiinnosta meitä, heitimme sen pois alikyselyn palautetuista sarakkeista.

Tekemistä on enää vähän jäljellä - laita DELETE käyttämään saamaamme sarjaa:

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

Tarkastetaanpa itse:

[katso selittää.tensor.ru]

Kyllä, kaikki on oikein: 3 tietuettamme valittiin yhdelle koko taulukon sekvenssiskannaukselle, ja Delete-solmua käytettiin tietojen etsimiseen. yksi passi Tid Scanilla:

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

Jos tyhjensit paljon tietueita, älä unohda suorittaa VACUUM ANALYZE.

Tarkistetaan isompi taulukko ja suurempi määrä kaksoiskappaleita:

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;

[katso selittää.tensor.ru]

Joten menetelmä toimii onnistuneesti, mutta sitä on käytettävä varoen. Koska jokaista poistettavaa tietuetta kohti luetaan yksi tietosivu Tid Scanissa ja yksi Delete-ohjelmassa.

Lähde: will.com