Vymazanie záznamov klonov z tabuľky bez PK

Sú situácie, kedy do tabuľky bez primárneho kľúča alebo nejaký iný jedinečný index, kvôli prehliadnutiu sú zahrnuté kompletné klony už existujúcich záznamov.

Napríklad hodnoty chronologickej metriky sa zapíšu do PostgreSQL pomocou streamu COPY a potom dôjde k náhlemu zlyhaniu a znova príde časť úplne identických údajov.

Ako zbaviť databázu nepotrebných klonov?

Keď PK nie je pomocník

Najjednoduchším spôsobom je v prvom rade zabrániť takejto situácii. Napríklad rolujte PRIMARY KEY. Ale to nie je vždy možné bez zvýšenia objemu uložených dát.

Ak je napríklad presnosť zdrojového systému vyššia ako presnosť poľa v databáze:

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}

Všimli ste si? Odpočítavanie namiesto 00:00:02 bolo zaznamenané v databáze s ts o sekundu skôr, ale z hľadiska aplikácie zostalo celkom platné (koniec koncov, hodnoty údajov sú iné!).

Samozrejme, že to dokážete PK(metrika; ts) - ale potom dostaneme konflikty pri vkladaní platných údajov.

Môže to urobiť PK(metrika; ts; údaje) - tým sa ale značne zväčší jeho objem, ktorý nevyužijeme.

Najsprávnejšou možnosťou je preto urobiť si pravidelný nejedinečný index (metrické, ts) a riešiť problémy až potom, ak sa vyskytnú.

"Klonická vojna sa začala"

Stala sa nejaká nehoda a teraz musíme zničiť záznamy o klonoch zo stola.

Modelujme pôvodné dáta:

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

Tu sa nám trikrát triasla ruka, zaseklo sa Ctrl+V a teraz...

Po prvé, pochopme, že naša tabuľka môže byť veľmi veľká, takže keď nájdeme všetky klony, je vhodné, aby sme ich vymazali doslova „strčením prsta“ konkrétne záznamy bez toho, aby ste ich znova hľadali.

A existuje taký spôsob - toto oslovovanie ctid, fyzický identifikátor konkrétneho záznamu.

To znamená, že v prvom rade musíme zhromaždiť ctid záznamov v kontexte úplného obsahu riadku tabuľky. Najjednoduchšou možnosťou je preniesť celý riadok do textu:

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

Je možné neobsadiť?V zásade je to vo väčšine prípadov možné. Kým nezačnete používať polia v tejto tabuľke typy bez operátora rovnosti:

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

Áno, okamžite vidíme, že ak je v poli viac ako jeden záznam, sú to všetky klony. Nechajme ich:

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)

Pre tých, ktorí radi píšu kratšieMôžete to napísať aj takto:

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

Keďže samotná hodnota serializovaného reťazca pre nás nie je zaujímavá, jednoducho sme ju vyhodili z vrátených stĺpcov poddotazu.

Zostáva urobiť len malý kúsok – prinútiť DELETE použiť sadu, ktorú sme dostali:

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

Preverme si:

[pozrite sa na explain.tensor.ru]

Áno, všetko je správne: naše 3 záznamy boli vybrané pre jediný Seq Scan celej tabuľky a na vyhľadávanie údajov bol použitý uzol Delete jeden prechod s Tid Scan:

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

Ak ste vymazali veľa záznamov, nezabudnite spustiť VACUUM ANALYZE.

Pozrime sa na väčšiu tabuľku a väčší počet duplikátov:

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;

[pozrite sa na explain.tensor.ru]

Metóda teda funguje úspešne, ale musí sa používať s určitou opatrnosťou. Pretože pre každý vymazaný záznam existuje jedna dátová stránka načítaná v Tid Scan a jedna v Delete.

Zdroj: hab.com