Klon records aus engem Dësch ouni PK läschen

Et gi Situatiounen wann op en Dësch ouni primär Schlëssel oder eng aner eenzegaarteg Index, wéinst enger Iwwersiicht, komplett clones vun schonn bestehend records abegraff.

Zum Beispill ginn d'Wäerter vun enger chronologescher Metrik an PostgreSQL mat engem COPY Stream geschriwwe, an da gëtt et e plötzlechen Echec, an en Deel vun de komplett identesche Donnéeën kommen erëm.

Wéi kann een d'Datebank vun onnéidege Klonen befreien?

Wann PK keen Helfer ass

Deen einfachste Wee ass fir esou eng Situatioun iwwerhaapt ze verhënneren. Zum Beispill, Rullo PRIMÄR KEY. Mä dëst ass net ëmmer méiglech ouni de Volume vun gespäichert Donnéeën Erhéijung.

Zum Beispill, wann d'Genauegkeet vum Quellsystem méi héich ass wéi d'Genauegkeet vum Feld an der Datebank:

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}

Hutt Dir gemierkt? De Countdown amplaz 00:00:02 gouf an der Datebank mat ts eng Sekonn virdru opgeholl, awer blouf ganz gëlteg aus enger Applikatioun Siicht (schliisslech sinn d'Datewäerter anescht!).

Natierlech kënnt Dir et maachen PK(metrisch, ts) - awer da kréie mir Insertiounskonflikter fir valabel Donnéeën.

Kann maachen PK(Metrisch, ts, Daten) - awer dëst wäert säi Volume staark erhéijen, wat mir net benotzen.

Dofir ass déi richteg Optioun fir e regelméissegen net eenzegaartegen Index ze maachen (metresch, ts) a këmmere sech mat Probleemer no der Tatsaach wa se entstinn.

"De klonesche Krich huet ugefaang"

Eng Aart Accident ass geschitt, an elo musse mir d'Klonrecords vum Dësch zerstéieren.

Loosst eis d'Original Daten modelléieren:

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

Hei huet eis Hand dräimol geziddert, Ctrl+V hänke bliwwen, an elo...

Als éischt, loosst eis verstoen datt eisen Dësch ganz grouss ka sinn, also nodeems mer all d'Klone fonnt hunn, ass et unzeroden eis wuertwiertlech "e Fanger ze pochen" fir ze läschen spezifesch records ouni se nei ze sichen.

An et gëtt esou e Wee - dëst adresséieren duerch ctid, de kierperlechen Identifizéierer vun engem spezifesche Rekord.

Dat ass, éischtens, mir mussen d'ctid vun records am Kontext vun der komplett Inhalt vun der Tabell Zeil sammelen. Déi einfachst Optioun ass déi ganz Linn an den Text ze werfen:

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

Ass et méiglech net ze casten?Am Prinzip ass et an de meeschte Fäll méiglech. Bis Dir ufänkt Felder an dëser Tabell ze benotzen Zorte ouni Gläichheet Bedreiwer:

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

Jo, mir gesinn direkt datt wann et méi wéi eng Entrée an der Array ass, dat sinn all Klonen. Loosst eis se just loossen:

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)

Fir déi, déi gär méi kuerz schreiwenDir kënnt et och esou schreiwen:

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

Well de Wäert vun der serialiséierter String selwer fir eis net interessant ass, hu mir et einfach aus de zréckgeschéckte Kolonnen vun der Ënnerquery erausgehäit.

Et bleift just e bëssen ze maachen - loosst DELETE de Set benotzen dee mir kruten:

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

Loosst eis selwer kontrolléieren:

[kuckt op explain.tensor.ru]

Jo, alles ass richteg: eis 3 Opzeechnunge goufen fir den eenzegen Seq Scan vun der ganzer Tabell ausgewielt, an den Delete Node gouf benotzt fir no Daten ze sichen eenzege Pass mat Tid Scan:

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

Wann Dir vill Rekorder geläscht hutt, vergiesst net VACUUM ANALYSE ze lafen.

Loosst eis kucken fir e méi groussen Dësch a mat enger méi grousser Unzuel vun Duplikaten:

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;

[kuckt op explain.tensor.ru]

Also, d'Methode funktionnéiert erfollegräich, awer et muss mat e puer Vorsicht benotzt ginn. Well fir all Rekord déi geläscht gëtt, gëtt et eng Donnéeën Säit gelies an Tid Scan, an een am Läschen.

Source: will.com