Kloonikirjete kustutamine tabelist ilma PK-ta

On olukordi, kus primaarvõtmeta tabelisse või mõni muu kordumatu indeks, millesse lisatakse tõrke tõttu juba olemasolevate kirjete täielikud kloonid.

Näiteks kronoloogilise mõõdiku väärtused kirjutatakse COPY voo abil PostgreSQL-i ja siis tekib ootamatu rike ja osa täiesti identsetest andmetest saabub uuesti.

Kuidas vabastada andmebaas tarbetutest kloonidest?

Kui PK pole abimees

Lihtsaim viis on ennetada sellise olukorra tekkimist. Näiteks veeretage PRIMARY KEY. Kuid see pole alati võimalik ilma salvestatud andmete mahtu suurendamata.

Näiteks kui lähtesüsteemi täpsus on suurem kui andmebaasi välja täpsus:

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}

Kas sa märkasid? Pöördloendus 00:00:02 asemel registreeriti andmebaasis ts-ga sekund varem, kuid jäi rakenduse seisukohast üsna kehtima (andmeväärtused on ju erinevad!).

Muidugi saate seda teha PK(meetria, ts) - kuid siis saame kehtivate andmete sisestuskonfliktid.

Võib teha PK(mõõdik, ts, andmed) - kuid see suurendab oluliselt selle mahtu, mida me ei kasuta.

Seetõttu on kõige õigem variant teha tavaline mitteunikaalne indeks (meetria, ts) ja tegelda probleemidega tagantjärele, kui need tekivad.

"Klooniline sõda on alanud"

Juhtus mingi õnnetus ja nüüd peame kloonikirjed tabelist hävitama.

Modelleerime algandmed:

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

Siin värises meie käsi kolm korda, Ctrl+V jäi kinni ja nüüd...

Esiteks mõistame, et meie tabel võib olla väga suur, nii et pärast kõigi kloonide leidmist on soovitatav kustutamiseks sõna otseses mõttes "näpuga torkida". konkreetseid kirjeid ilma neid uuesti otsimata.

Ja on olemas selline viis – see adresseerimine ctid, konkreetse kirje füüsiline identifikaator.

See tähendab, et kõigepealt peame koguma kirjete ctid tabelirea täieliku sisu kontekstis. Lihtsaim võimalus on kogu rida tekstiks valada:

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

Kas on võimalik mitte valada?Põhimõtteliselt on see enamikul juhtudel võimalik. Kuni hakkate selle tabeli välju kasutama tüübid ilma võrdõiguslikkuse operaatorita:

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

Jah, me näeme kohe, et kui massiivis on rohkem kui üks kirje, on need kõik kloonid. Jätame need lihtsalt:

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)

Neile, kellele meeldib lühemalt kirjutadaVõite selle kirjutada ka nii:

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

Kuna serialiseeritud stringi väärtus ei ole meile huvitav, siis viskasime selle lihtsalt alampäringu tagastatud veergudest välja.

Teha on jäänud veel veidi – pange DELETE kasutama saadud komplekti:

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

Kontrollime ennast:

[vaadake saidil magyarázat.tensor.ru]

Jah, kõik on õige: meie 3 kirjet valiti kogu tabeli ainsa Seq Scan jaoks ja andmete otsimiseks kasutati sõlme Kustuta ühekordne pääse Tid Scaniga:

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

Kui kustutasite palju kirjeid, ärge unustage käivitada VACUUM ANALYZE.

Kontrollime suuremat tabelit ja suurema arvu duplikaate:

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;

[vaadake saidil magyarázat.tensor.ru]

Niisiis, meetod töötab edukalt, kuid selle kasutamisel tuleb olla ettevaatlik. Sest iga kustutatud kirje kohta loetakse Tid Scanis üks andmeleht ja üks Delete'is.

Allikas: www.habr.com