Esborrar registres de clons d'una taula sense PK

Hi ha situacions en què a una taula sense clau primària o algun altre índex únic, a causa d'un descuit, s'inclouen clons complets de registres ja existents.

Per exemple, els valors d'una mètrica cronològica s'escriuen a PostgreSQL mitjançant un flux COPY, i després hi ha un error sobtat i una part de les dades completament idèntiques arriba de nou.

Com desfer la base de dades de clons innecessaris?

Quan PK no és un ajudant

La manera més senzilla és evitar que aquesta situació es produeixi en primer lloc. Per exemple, tireu CLAU PRIMÀRIA. Però això no sempre és possible sense augmentar el volum de dades emmagatzemades.

Per exemple, si la precisió del sistema font és superior a la precisió del camp de la base de dades:

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}

Te n'has adonat? El compte enrere en lloc de 00:00:02 es va registrar a la base de dades amb ts un segon abans, però es va mantenir força vàlid des del punt de vista de l'aplicació (al cap i a la fi, els valors de les dades són diferents!).

Per descomptat que pots fer-ho PK(mètrica, ts) - però llavors obtindrem conflictes d'inserció per a dades vàlides.

Puc fer PK(mètrica, ts, dades) - però això augmentarà molt el seu volum, que no utilitzarem.

Per tant, l'opció més correcta és fer un índex normal no únic (mètrica, ts) i tractar els problemes després del fet si sorgeixen.

"La guerra clònica ha començat"

Va passar algun tipus d'accident i ara hem de destruir els registres de clons de la taula.

Modelem les dades originals:

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

Aquí ens va tremolar la mà tres vegades, Ctrl+V es va quedar encallat i ara...

En primer lloc, entenem que la nostra taula pot ser molt gran, de manera que després de trobar tots els clons, és recomanable que literalment "picar-nos el dit" per eliminar-los. registres específics sense tornar-los a cercar.

I hi ha una manera - aquesta adreçament per ctid, l'identificador físic d'un registre específic.

És a dir, primer de tot, hem de recopilar el ctid dels registres en el context del contingut complet de la fila de la taula. L'opció més senzilla és llançar tota la línia en 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)"}

És possible no llançar?En principi, és possible en la majoria dels casos. Fins que comenceu a utilitzar els camps d'aquesta taula tipus sense operador d'igualtat:

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

Sí, de seguida veiem que si hi ha més d'una entrada a la matriu, tots aquests són clons. Només els deixem:

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)

Per als que els agrada escriure més curtTambé pots escriure-ho així:

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

Com que el valor de la cadena serialitzada en si no ens interessa, simplement l'hem llençat de les columnes retornades de la subconsulta.

Només queda una mica per fer: feu que DELETE utilitzi el conjunt que hem rebut:

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

Comprovem-nos:

[veure explica.tensor.ru]

Sí, tot és correcte: els nostres 3 registres es van seleccionar per a una única exploració seqüència de tota la taula i el node Suprimeix es va utilitzar per cercar dades. una sola passada amb Tid Scan:

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

Si heu esborrat molts registres, no us oblideu d'executar VACUUM ANALYSE.

Comprovem si hi ha una taula més gran i amb un nombre més gran de duplicats:

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;

[veure explica.tensor.ru]

Per tant, el mètode funciona correctament, però s'ha d'utilitzar amb certa precaució. Perquè per cada registre que s'elimina, hi ha una pàgina de dades llegida a Tid Scan i una altra a Eliminar.

Font: www.habr.com