Pag-clear ng mga clone record mula sa isang table na walang PK

May mga sitwasyon kung kailan sa isang talahanayan na walang pangunahing susi o ilang iba pang natatanging index, dahil sa isang pangangasiwa, ang mga kumpletong clone ng mga umiiral nang record ay kasama.

Halimbawa, ang mga halaga ng isang kronolohikal na sukatan ay isinulat sa PostgreSQL gamit ang isang COPY stream, at pagkatapos ay mayroong biglaang pagkabigo, at bahagi ng ganap na magkaparehong data ang dumating muli.

Paano mapupuksa ang database ng mga hindi kinakailangang clone?

Kapag hindi katulong si PK

Ang pinakamadaling paraan ay upang maiwasan ang ganitong sitwasyon na mangyari sa unang lugar. Halimbawa, roll PRIMARY KEY. Ngunit hindi ito laging posible nang hindi pinapataas ang dami ng nakaimbak na data.

Halimbawa, kung ang katumpakan ng source system ay mas mataas kaysa sa katumpakan ng field sa database:

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}

Napansin mo ba? Ang countdown sa halip na 00:00:02 ay naitala sa database na may ts isang segundo nang mas maaga, ngunit nanatiling wasto mula sa punto ng view ng aplikasyon (pagkatapos ng lahat, ang mga halaga ng data ay naiiba!).

Syempre kaya mo yan PK(sukatan, ts) - ngunit pagkatapos ay makakakuha tayo ng mga salungatan sa pagpapasok para sa wastong data.

Magagawa mo PK(sukatan, ts, data) - ngunit ito ay lubos na magpapataas ng dami nito, na hindi namin gagamitin.

Samakatuwid, ang pinakatamang opsyon ay ang gumawa ng regular na hindi natatanging index (sukatan, ts) at harapin ang mga problema pagkatapos ng katotohanan kung ito ay lumitaw.

"Nagsimula na ang clonic war"

Ilang uri ng aksidente ang nangyari, at ngayon kailangan nating sirain ang mga clone record mula sa talahanayan.

Imodelo natin ang orihinal na data:

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

Narito ang aming kamay ay nanginginig ng tatlong beses, ang Ctrl+V ay naipit, at ngayon...

Una, unawain natin na ang mesa natin ay maaaring maging napakalaki, kaya pagkatapos nating mahanap ang lahat ng mga clone, ipinapayong literal nating "sundutin ang ating daliri" para tanggalin. tiyak na mga talaan nang hindi muling hinahanap ang mga ito.

At mayroong isang paraan - ito pagtugon sa pamamagitan ng ctid, ang pisikal na pagkakakilanlan ng isang partikular na tala.

Iyon ay, una sa lahat, kailangan nating kolektahin ang ctid ng mga tala sa konteksto ng kumpletong nilalaman ng hilera ng talahanayan. Ang pinakasimpleng opsyon ay i-cast ang buong linya sa text:

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

Posible bang hindi mag-cast?Sa prinsipyo, posible sa karamihan ng mga kaso. Hanggang sa magsimula kang gumamit ng mga patlang sa talahanayang ito mga uri na walang equality operator:

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

Oo, nakita namin kaagad na kung mayroong higit sa isang entry sa array, ang lahat ng ito ay mga clone. Iwanan na lang natin sila:

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)

Para sa mga mahilig magsulat ng mas maikliMaaari mo ring isulat ito tulad nito:

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

Dahil ang halaga ng serialized string mismo ay hindi kawili-wili sa amin, itinapon lang namin ito sa mga ibinalik na column ng subquery.

Kaunti na lang ang natitira - gamitin ang DELETE sa set na natanggap namin:

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

Suriin natin ang ating sarili:

[tingnan sa explain.tensor.ru]

Oo, tama ang lahat: ang aming 3 tala ay pinili para sa tanging Seq Scan ng buong talahanayan, at ang Delete node ay ginamit upang maghanap ng data single pass na may Tid Scan:

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

Kung nag-clear ka ng maraming record, huwag kalimutang patakbuhin ang VACUUM ANALYZE.

Tingnan natin ang mas malaking talahanayan at may mas malaking bilang ng mga duplicate:

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;

[tingnan sa explain.tensor.ru]

Kaya, matagumpay na gumagana ang pamamaraan, ngunit dapat itong gamitin nang may pag-iingat. Dahil sa bawat tala na natanggal, mayroong isang pahina ng data na nabasa sa Tid Scan, at isa sa Tanggalin.

Pinagmulan: www.habr.com