Paglimpyo sa mga rekord sa clone gikan sa usa ka lamesa nga walay PK

Adunay mga sitwasyon kung kanus-a sa usa ka lamesa nga walay panguna nga yawe o uban pang talagsaon nga index, tungod sa usa ka oversight, ang mga kompleto nga clone sa naa na nga mga rekord gilakip.

Pananglitan, ang mga kantidad sa usa ka kronolohikal nga sukatan gisulat sa PostgreSQL gamit ang COPY stream, ug unya adunay kalit nga pagkapakyas, ug ang bahin sa hingpit nga parehas nga datos moabut pag-usab.

Giunsa pagtangtang ang database sa dili kinahanglan nga mga clone?

Kung dili katabang si PK

Ang labing sayon ​​nga paagi mao ang pagpugong sa ingon nga sitwasyon nga mahitabo sa unang dapit. Pananglitan, roll PRIMARY KEY. Apan dili kini kanunay nga posible kung wala’y pagdugang sa gidaghanon sa gitipig nga datos.

Pananglitan, kung ang katukma sa source system mas taas kaysa sa katukma sa 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}

Namatikdan ba nimo? Ang countdown imbes nga 00:00:02 girekord sa database nga adunay ts usa ka segundo sa sayo pa, apan nagpabilin nga balido gikan sa usa ka punto sa pagtan-aw sa aplikasyon (pagkahuman, ang mga kantidad sa datos lahi!).

Siyempre mahimo nimo kini PK(metric, ts) - apan unya makakuha kami mga panagsumpaki sa pagsulod alang sa balido nga datos.

Mahimo PK(metric, ts, data) - apan kini makadugang pag-ayo sa gidaghanon niini, nga dili namo gamiton.

Busa, ang labing husto nga kapilian mao ang paghimo sa usa ka regular nga dili talagsaon nga indeks (metric, ts) ug atubangon ang mga problema human sa kamatuoran kon kini motungha.

"Nagsugod na ang clonic war"

Usa ka matang sa aksidente ang nahitabo, ug karon kinahanglan natong gub-on ang mga clone record gikan sa lamesa.

Atong modelo ang orihinal nga datos:

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

Dinhi mikurog ang among kamot sa tulo ka beses, ang Ctrl+V natanggong, ug karon...

Una, atong sabton nga ang atong lamesa mahimong dako kaayo, mao nga human nato makit-an ang tanang mga clone, mas maayo nga literal nga "iduko ang atong tudlo" aron mapapas. espesipikong mga rekord nga walay pag-usisa pag-usab niini.

Ug adunay ingon nga paagi - kini pagtubag pinaagi sa ctid, ang pisikal nga identifier sa usa ka piho nga rekord.

Kana mao, una sa tanan, kinahanglan natong kolektahon ang ctid sa mga rekord sa konteksto sa kompleto nga sulod sa laray sa lamesa. Ang pinakasimple nga kapilian mao ang pagbutang sa tibuok linya ngadto sa teksto:

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 ba nga dili ihulog?Sa prinsipyo, kini posible sa kadaghanan nga mga kaso. Hangtud nga magsugod ka sa paggamit sa mga uma niini nga lamesa mga tipo nga walay 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 dayon namon nga kung adunay labaw pa sa usa ka entry sa array, kini tanan mga clone. Pasagdi lang nato 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 ganahan magsulat ug mugboMahimo usab nimo kini isulat sama niini:

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

Tungod kay ang bili sa serialized string mismo dili makapainteres kanamo, gilabay lang namo kini gikan sa gibalik nga mga kolum sa subquery.

Adunay usa ka gamay nga nahabilin nga buhaton - gamita ang DELETE sa set nga among nadawat:

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

Atong susihon ang atong kaugalingon:

[tan-awa sa explain.tensor.ru]

Oo, tama ang tanan: ang among 3 nga mga rekord gipili alang sa bugtong Seq Scan sa tibuuk nga lamesa, ug ang Delete node gigamit sa pagpangita sa datos single pass nga adunay Tid Scan:

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

Kung gihawiran nimo ang daghang mga rekord, ayaw kalimot sa pagdagan sa VACUUM ANALYZE.

Atong susihon ang usa ka mas dako nga lamesa ug adunay daghang gidaghanon sa 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;

[tan-awa sa explain.tensor.ru]

Busa, ang pamaagi malampuson nga nagtrabaho, apan kini kinahanglan nga gamiton uban ang pag-amping. Tungod kay sa matag rekord nga matangtang, adunay usa ka panid sa datos nga mabasa sa Tid Scan, ug usa sa Delete.

Source: www.habr.com