Rydning af klonposter fra en tabel uden PK

Der er situationer, når til en tabel uden en primær nøgle eller et andet unikt indeks, på grund af en forglemmelse, er komplette kloner af allerede eksisterende poster inkluderet.

For eksempel bliver værdierne af en kronologisk metrik skrevet ind i PostgreSQL ved hjælp af en COPY-stream, og så er der en pludselig fejl, og en del af de fuldstændig identiske data kommer igen.

Hvordan befrir man databasen for unødvendige kloner?

Når PK ikke er en hjælper

Den nemmeste måde er at forhindre en sådan situation i at opstå i første omgang. Rul for eksempel PRIMÆR NØGLE. Men dette er ikke altid muligt uden at øge mængden af ​​lagrede data.

For eksempel, hvis nøjagtigheden af ​​kildesystemet er højere end nøjagtigheden af ​​feltet i databasen:

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}

Lagde du mærke til det? Nedtællingen i stedet for 00:00:02 blev registreret i databasen med ts et sekund tidligere, men forblev ret gyldig fra et applikationssynspunkt (trods alt er dataværdierne anderledes!).

Selvfølgelig kan du gøre det PK(metrisk, ts) - men så får vi indsættelseskonflikter for gyldige data.

Kan gøre PK(metrisk, ts, data) - men dette vil i høj grad øge dens volumen, som vi ikke vil bruge.

Derfor er den mest korrekte mulighed at lave et almindeligt ikke-unik indeks (metrisk, ts) og håndtere problemer bagefter, hvis de opstår.

"Den kloniske krig er begyndt"

Der skete en eller anden form for ulykke, og nu skal vi ødelægge klonoptegnelserne fra bordet.

Lad os modellere de originale 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)
;

Her rystede vores hånd tre gange, Ctrl+V sad fast, og nu...

Lad os først forstå, at vores bord kan være meget stort, så efter at vi har fundet alle klonerne, er det tilrådeligt, at vi bogstaveligt talt "stikker i fingeren" for at slette specifikke poster uden at gensøge dem.

Og der er sådan en måde - denne adressering af ctid, den fysiske identifikator for en specifik post.

Det vil sige, at vi først og fremmest skal indsamle ctid af poster i sammenhæng med det komplette indhold af tabelrækken. Den enkleste mulighed er at støbe hele linjen ind i tekst:

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

Er det muligt ikke at kaste?I princippet er det muligt i de fleste tilfælde. Indtil du begynder at bruge felter i denne tabel typer uden ligestillingsoperatør:

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, vi ser straks, at hvis der er mere end én post i arrayet, er disse alle kloner. Lad os bare forlade dem:

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)

For dem, der kan lide at skrive kortereDu kan også skrive det sådan her:

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

Da værdien af ​​selve den serialiserede streng ikke er interessant for os, smed vi den simpelthen ud af de returnerede kolonner i underforespørgslen.

Der er kun lidt tilbage at gøre - få DELETE til at bruge det sæt, vi modtog:

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

Lad os tjekke os selv:

[se på explain.tensor.ru]

Ja, alt er korrekt: vores 3 poster blev udvalgt til den eneste Seq Scan af hele tabellen, og Slet-noden blev brugt til at søge efter data enkeltpas med Tid Scan:

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

Hvis du har ryddet mange poster, glem ikke at køre VACUUM ANALYZE.

Lad os se efter en større tabel og med et større antal dubletter:

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;

[se på explain.tensor.ru]

Så metoden fungerer med succes, men den skal bruges med en vis forsigtighed. For for hver post, der slettes, er der én dataside læst i Tid Scan, og én i Slet.

Kilde: www.habr.com