Effacer les enregistrements clonés d'une table sans PK

Il y a des situations où à une table sans clé primaire ou un autre index unique, en raison d'un oubli, des clones complets d'enregistrements déjà existants sont inclus.

Par exemple, les valeurs d'une métrique chronologique sont écrites dans PostgreSQL à l'aide d'un flux COPY, puis il y a un échec soudain et une partie des données complètement identiques arrive à nouveau.

Comment débarrasser la base de données des clones inutiles ?

Quand PK n'est pas une aide

Le moyen le plus simple est d’éviter qu’une telle situation ne se produise. Par exemple, lancez PRIMARY KEY. Mais cela n’est pas toujours possible sans augmenter le volume des données stockées.

Par exemple, si la précision du système source est supérieure à la précision du champ dans la base de données :

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}

As-tu remarqué? Le compte à rebours au lieu de 00:00:02 a été enregistré dans la base de données avec ts une seconde plus tôt, mais est resté tout à fait valable d'un point de vue applicatif (après tout, les valeurs des données sont différentes !).

Bien sûr, tu peux le faire PK(métrique, ts) - mais nous aurons alors des conflits d'insertion pour des données valides.

Peut faire PK (métrique, ts, données) - mais cela augmentera considérablement son volume, que nous n'utiliserons pas.

Par conséquent, l'option la plus correcte consiste à créer un index régulier non unique (métrique, ts) et régler les problèmes après coup s'ils surviennent.

"La guerre clonique a commencé"

Une sorte d'accident s'est produit et nous devons maintenant détruire les enregistrements clonés de la table.

Modélisons les données originales :

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

Ici notre main a tremblé trois fois, Ctrl+V est resté bloqué, et maintenant...

Tout d'abord, comprenons que notre table peut être très grande, donc après avoir trouvé tous les clones, il nous est conseillé de littéralement « pousser notre doigt » pour supprimer des enregistrements spécifiques sans les rechercher.

Et il existe un tel moyen - celui-ci adressage par ctid, l'identifiant physique d'un enregistrement spécifique.

Autrement dit, nous devons tout d'abord collecter le ctid des enregistrements dans le contexte du contenu complet de la ligne du tableau. L'option la plus simple consiste à convertir la ligne entière en texte :

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

Est-il possible de ne pas lancer de casting ?En principe, c'est possible dans la plupart des cas. Jusqu'à ce que vous commenciez à utiliser les champs de cette table types sans opérateur d'égalité:

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

Oui, nous voyons immédiatement que s'il y a plus d'une entrée dans le tableau, ce sont toutes des clones. Laissons-les simplement :

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)

Pour ceux qui aiment écrire plus courtVous pouvez aussi l'écrire comme ceci :

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

Étant donné que la valeur de la chaîne sérialisée elle-même ne nous intéresse pas, nous l'avons simplement supprimée des colonnes renvoyées par la sous-requête.

Il ne reste plus qu'un petit peu à faire : faites en sorte que DELETE utilise l'ensemble que nous avons reçu :

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

Vérifions nous-mêmes :

[regardez expliquer.tensor.ru]

Oui, tout est correct : nos 3 enregistrements ont été sélectionnés pour le seul Seq Scan de toute la table, et le nœud Supprimer a été utilisé pour rechercher des données un seul passage avec Tid Scan:

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

Si vous avez effacé beaucoup de dossiers, n'oubliez pas de lancer VACUUM ANALYZE.

Vérifions une table plus grande et avec un plus grand nombre de doublons :

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;

[regardez expliquer.tensor.ru]

La méthode fonctionne donc avec succès, mais elle doit être utilisée avec une certaine prudence. Parce que pour chaque enregistrement supprimé, il y a une page de données lue dans Tid Scan et une dans Supprimer.

Source: habr.com