Clearing clone records út in tabel sûnder PK

Der binne situaasjes wannear nei in tabel sûnder in primêre kaai of in oare unike yndeks, troch in tafersjoch, binne folsleine klonen fan al besteande records opnommen.

Bygelyks, de wearden fan in gronologyske metrik wurde skreaun yn PostgreSQL mei in COPY-stream, en dan is d'r in hommelse mislearring, en in diel fan 'e folslein identike gegevens komt wer oan.

Hoe kinne jo de databank fan ûnnedige klonen ferwiderje?

As PK is gjin helper

De maklikste manier is om foar te kommen dat sa'n situaasje yn it foarste plak komt. Bygelyks, roll PRIMARY KEY. Mar dit is net altyd mooglik sûnder it fergrutsjen fan it folume fan opsleine gegevens.

Bygelyks, as de krektens fan it boarnesysteem heger is as de krektens fan it fjild yn 'e databank:

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}

Hawwe jo opfallen? De countdown ynstee fan 00:00:02 waard opnommen yn 'e databank mei ts in sekonde earder, mar bleau frij jildich út in tapassing eachpunt (ommers, de gegevens wearden binne oars!).

Fansels kinne jo it dwaan PK(metrysk, ts) - mar dan krije wy ynfoegje konflikten foar jildige gegevens.

Dwaan kin PK(metrysk, ts, gegevens) - mar dit sil har folume sterk ferheegje, dy't wy net sille brûke.

Dêrom is de meast korrekte opsje om in reguliere net-unike yndeks te meitsjen (metrysk, ts) en omgean mei problemen nei it feit as se ûntsteane.

"De klonyske oarloch is begon"

Der barde in soarte fan ûngelok, en no moatte wy de kloonrecords fan 'e tafel ferneatigje.

Litte wy de orizjinele gegevens modellearje:

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

Hjir trille ús hân trije kear, Ctrl+V kaam fêst te sitten, en no...

Litte wy earst begripe dat ús tabel heul grut kin wêze, dus nei't wy alle klonen hawwe fûn, is it oan te rieden om letterlik "ús finger te stekken" om te wiskjen spesifike records sûnder se opnij te sykjen.

En der is sa'n manier - dit adressering troch ctid, de fysike identifier fan in spesifyk record.

Dat is, earst fan alles, wy moatte sammelje de ctid fan records yn 'e kontekst fan' e folsleine ynhâld fan 'e tabel rige. De ienfâldichste opsje is om de heule line yn tekst te casten:

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

Is it mooglik net te casten?Yn prinsipe is it mooglik yn de measte gefallen. Oant jo begjinne te brûken fjilden yn dizze tabel typen sûnder gelikensens 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

Ja, wy sjogge fuortendaliks dat as d'r mear dan ien yngong yn 'e array is, dit allegear klonen binne. Litte wy se mar litte:

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)

Foar dyjingen dy't graach koarter skriuweJo kinne it ek sa skriuwe:

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

Sûnt de wearde fan 'e serialisearre tekenrige sels net ynteressant is foar ús, hawwe wy it gewoan út' e weromjûne kolommen fan 'e subquery smiten.

D'r is mar in bytsje te dwaan - lit DELETE de set brûke dy't wy krigen hawwe:

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

Litte wy ússels kontrolearje:

[sjoch op explain.tensor.ru]

Ja, alles is korrekt: ús 3 records waarden selektearre foar de ienige Seq Scan fan 'e heule tabel, en de knooppunt wiskje waard brûkt om te sykjen nei gegevens inkele pas mei Tid Scan:

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

As jo ​​​​in protte records wiske hawwe, ferjit net om VACUUM ANALYSE út te fieren.

Litte wy kontrolearje op in gruttere tabel en mei in grutter oantal 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;

[sjoch op explain.tensor.ru]

Dat, de metoade wurket mei súkses, mar it moat brûkt wurde mei wat foarsichtigens. Want foar eltse record dat wurdt wiske, der is ien gegevens side lêzen yn Tid Scan, en ien yn wiskje.

Boarne: www.habr.com