Sletting av kloneposter fra en tabell uten PK

Det er situasjoner når til en tabell uten en primærnøkkel eller en annen unik indeks, på grunn av en forglemmelse, er komplette kloner av allerede eksisterende poster inkludert.

For eksempel blir verdiene til en kronologisk metrikk skrevet inn i PostgreSQL ved hjelp av en COPY-strøm, og så er det en plutselig feil, og en del av de helt identiske dataene kommer igjen.

Hvordan kvitte databasen for unødvendige kloner?

Når PK ikke er en hjelper

Den enkleste måten er å forhindre at en slik situasjon oppstår i utgangspunktet. Rull for eksempel PRIMÆR NØKKEL. Men dette er ikke alltid mulig uten å øke volumet av lagrede data.

For eksempel, hvis nøyaktigheten til kildesystemet er høyere enn nøyaktigheten til 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}

La du merke til? Nedtellingen i stedet for 00:00:02 ble registrert i databasen med ts et sekund tidligere, men forble ganske gyldig fra et applikasjonssynspunkt (tross alt er dataverdiene forskjellige!).

Selvfølgelig kan du gjøre det PK(metrisk, ts) - men da vil vi få innsettingskonflikter for gyldige data.

Kan gjøre PK(metrisk, ts, data) - men dette vil øke volumet kraftig, som vi ikke kommer til å bruke.

Derfor er det mest korrekte alternativet å lage en vanlig ikke-unik indeks (metrisk, ts) og håndtere problemer i etterkant hvis de oppstår.

"Den kloniske krigen har begynt"

En slags ulykke skjedde, og nå må vi ødelegge klonepostene fra bordet.

La oss modellere de originale dataene:

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 skalv hånden vår tre ganger, Ctrl+V ble sittende fast, og nå...

Først, la oss forstå at bordet vårt kan være veldig stort, så etter at vi har funnet alle klonene, er det tilrådelig for oss å bokstavelig talt "stikke fingeren" for å slette spesifikke poster uten å søke på nytt i dem.

Og det er en slik måte - dette adressering av ctid, den fysiske identifikatoren til en bestemt post.

Det vil si at vi først og fremst må samle inn ctid av poster i sammenheng med det fullstendige innholdet i tabellraden. Det enkleste alternativet er å kaste hele linjen til 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 mulig å ikke kaste?I prinsippet er det mulig i de fleste tilfeller. Helt til du begynner å bruke feltene i denne tabellen typer uten likestillingsoperatø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 umiddelbart at hvis det er mer enn én oppføring i matrisen, er disse alle kloner. La oss bare la dem ligge:

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 de som liker å skrive kortereDu kan også skrive det slik:

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

Siden verdien av den serialiserte strengen i seg selv ikke er interessant for oss, kastet vi den ganske enkelt ut av de returnerte kolonnene i underspørringen.

Det er bare litt igjen å gjøre - få DELETE til å bruke settet vi mottok:

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

La oss sjekke oss selv:

[se på explain.tensor.ru]

Ja, alt er riktig: våre 3 poster ble valgt for den eneste Seq Scan av hele tabellen, og Slett-noden ble brukt til å søke etter data enkeltpass med Tid Scan:

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

Hvis du slettet mange poster, ikke glem å kjøre VACUUM ANALYZE.

La oss se etter et større bord og med et større antall duplikater:

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 vellykket, men den må brukes med en viss forsiktighet. For for hver post som slettes, er det én dataside som leses i Tid Scan, og én i Slett.

Kilde: www.habr.com