Mbusak cathetan kloning saka meja tanpa PK

Ana kahanan nalika menyang meja tanpa kunci utama utawa sawetara indeks unik liyane, amarga saka pengawasan, tiron lengkap cathetan sing wis ana wis klebu.

Contone, nilai metrik kronologis ditulis ing PostgreSQL nggunakake stream COPY, banjur ana kegagalan dadakan, lan bagean saka data sing padha teka maneh.

Kepiye carane mbusak database saka klon sing ora perlu?

Nalika PK dudu pembantu

Cara sing paling gampang yaiku nyegah kahanan kaya ngono ing wiwitan. Contone, muter PRIMARY KEY. Nanging iki ora mesthi bisa tanpa nambah volume data sing disimpen.

Contone, yen akurasi sistem sumber luwih dhuwur tinimbang akurasi lapangan ing basis data:

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}

Apa sampeyan ngeweruhi? Countdown tinimbang 00:00:02 dicathet ing basis data kanthi ts sedetik sadurunge, nanging tetep bener saka sudut pandang aplikasi (sawise kabeh, nilai data beda-beda!).

Mesthi wae sampeyan bisa nindakake PK (metrik, ts) - nanging banjur bakal entuk konflik sisipan kanggo data sing bener.

Bisa nindakake PK (metrik, ts, data) - nanging iki bakal nambah volume, sing ora bakal digunakake.

Mulane, pilihan sing paling bener yaiku nggawe indeks non-unik biasa (metrik, ts) lan ngatasi masalah sawise kasunyatan yen padha njedhul.

"Perang klonik wis diwiwiti"

Sawetara jenis kacilakan kedaden, lan saiki kita kudu numpes cathetan Klone saka meja.

Ayo model data asli:

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

Ing kene tangan kita gumeter ping telu, Ctrl+V macet, lan saiki...

Pisanan, ayo dimangerteni manawa meja kita bisa dadi gedhe banget, mula sawise nemokake kabeh klon, luwih becik kita "nyodok driji" kanggo mbusak. cathetan tartamtu tanpa maneh nelusuri.

Lan ana cara - iki alamat dening ctid, pengenal fisik saka rekaman tartamtu.

Sing, pisanan kabeh, kita kudu ngumpulake ctid saka cathetan ing konteks isi lengkap baris tabel. Pilihan sing paling gampang yaiku nglebokake kabeh baris menyang teks:

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

Apa bisa ora kanggo cast?Ing asas, iku bisa ing paling kasus. Nganti sampeyan miwiti nggunakake kolom ing tabel iki jinis tanpa operator kesetaraan:

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

Ya, kita langsung weruh yen ana luwih saka siji entri ing array, iki kabeh klon. Ayo padha ninggalake:

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)

Kanggo sing seneng nulis luwih cendhekSampeyan uga bisa nulis kaya iki:

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

Amarga regane senar serial dhewe ora menarik kanggo kita, kita mung mbuwang saka kolom bali saka subquery.

Mung sawetara sing kudu ditindakake - nggawe DELETE nggunakake set sing ditampa:

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

Ayo priksa dhewe:

[deleng ing explain.tensor.ru]

Ya, kabeh bener: 3 cathetan kita dipilih mung kanggo Seq Scan kabeh tabel, lan simpul Busak digunakake kanggo nggoleki data pass siji karo Tid Scan:

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

Yen sampeyan mbusak akeh cathetan, aja lali mbukak VACUUM ANALYZE.

Ayo priksa tabel sing luwih gedhe lan kanthi jumlah duplikat sing luwih akeh:

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;

[deleng ing explain.tensor.ru]

Dadi, cara kasebut sukses, nanging kudu digunakake kanthi ati-ati. Amarga saben rekaman sing dibusak, ana siji kaca data sing diwaca ing Tid Scan, lan siji ing Busak.

Source: www.habr.com