Hreinsar klónaskrár úr töflu án PK

Það eru aðstæður þegar í töflu án aðallykils eða einhverja aðra einstaka vísitölu, vegna eftirlits, eru heill klón af skrám sem þegar eru til innifalin.

Til dæmis eru gildi tímatalsmælikvarða skrifuð inn í PostgreSQL með því að nota COPY straum, og þá kemur skyndilega bilun og hluti af alveg eins gögnum kemur aftur.

Hvernig á að losa gagnagrunninn við óþarfa klóna?

Þegar PK er ekki aðstoðarmaður

Auðveldasta leiðin er að koma í veg fyrir að slíkt ástand komi upp í fyrsta lagi. Til dæmis, rúllaðu AÐALLYKLI. En þetta er ekki alltaf mögulegt án þess að auka magn geymdra gagna.

Til dæmis, ef nákvæmni frumkerfisins er meiri en nákvæmni reitsins í gagnagrunninum:

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}

Tókstu eftir því? Niðurtalning í stað 00:00:02 var skráð í gagnagrunninn með ts sekúndu fyrr, en hélst nokkuð gild frá sjónarhóli umsóknar (enda eru gagnagildin önnur!).

Auðvitað geturðu gert það PK(mæling, ts) - en þá fáum við innsetningarátök fyrir gild gögn.

Getur gert PK(mæling, ts, gögn) - en þetta mun stórauka rúmmál þess, sem við munum ekki nota.

Þess vegna er réttasti kosturinn að búa til venjulega ósérstæða vísitölu (mæling, ts) og takast á við vandamál í kjölfarið ef þau koma upp.

„Klóníska stríðið er hafið“

Einhvers konar slys gerðist og nú verðum við að eyða klónaskránum af borðinu.

Við skulum móta upprunalegu gögnin:

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

Hér skalf hönd okkar þrisvar sinnum, Ctrl+V festist, og nú...

Í fyrsta lagi skulum við skilja að borðið okkar getur verið mjög stórt, svo eftir að við höfum fundið öll klónin, er ráðlegt fyrir okkur að bókstaflega „potta í fingri“ til að eyða tilteknar skrár án þess að leita aftur í þeim.

Og það er svoleiðis - þetta ávarp með ctid, líkamlegt auðkenni tiltekinnar skráar.

Það er, fyrst og fremst þurfum við að safna ctid færslur í samhengi við heildar innihald töflulínunnar. Einfaldasti kosturinn er að varpa allri línunni í texta:

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

Er hægt að kasta ekki?Í grundvallaratriðum er það mögulegt í flestum tilfellum. Þar til þú byrjar að nota reiti í þessari töflu tegundir án jafnréttisaðila:

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á, við sjáum strax að ef það eru fleiri en ein færsla í fylkinu, þá eru þetta allt klón. Við skulum bara skilja þá eftir:

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)

Fyrir þá sem vilja skrifa styttraÞú getur líka skrifað þetta svona:

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

Þar sem gildi serialized strengsins sjálfs er ekki áhugavert fyrir okkur, hentum við því einfaldlega út úr skiluðum dálkum undirfyrirspurnarinnar.

Það er aðeins eftir að gera - láttu DELETE nota settið sem við fengum:

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

Við skulum athuga okkur sjálf:

[horfðu á explain.tensor.ru]

Já, allt er rétt: 3 færslurnar okkar voru valdar fyrir eina Seq Scan af allri töflunni og Eyða hnúturinn var notaður til að leita að gögnum stakur passi með Tid Scan:

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

Ef þú hreinsaðir margar skrár, ekki gleyma að keyra VACUUM ANALYZE.

Við skulum athuga hvort það sé stærra borð og með fleiri afritum:

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;

[horfðu á explain.tensor.ru]

Þannig að aðferðin virkar með góðum árangri, en hún verður að nota með nokkurri varúð. Vegna þess að fyrir hverja skrá sem er eytt er ein gagnasíða lesin í Tid Scan og ein í Delete.

Heimild: www.habr.com