Cancellazione dei record clone da una tabella senza PK

Ci sono situazioni in cui ad una tabella senza chiave primaria o qualche altro indice unico, a causa di una svista, vengono inclusi cloni completi di record già esistenti.

Ad esempio, i valori di una metrica cronologica vengono scritti in PostgreSQL utilizzando un flusso COPY, quindi si verifica un errore improvviso e parte dei dati completamente identici ritorna di nuovo.

Come liberare il database dai cloni non necessari?

Quando PK non è un aiutante

Il modo più semplice è innanzitutto evitare che una situazione del genere si verifichi. Ad esempio, lancia CHIAVE PRIMARIA. Ma ciò non è sempre possibile senza aumentare il volume dei dati archiviati.

Ad esempio, se la precisione del sistema di origine è superiore alla precisione del campo nel database:

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}

Hai notato? Il conto alla rovescia invece di 00:00:02 è stato registrato nel database con ts un secondo prima, ma è rimasto abbastanza valido dal punto di vista applicativo (dopotutto i valori dei dati sono diversi!).

Certo che puoi farlo PK(metrico, ts) - ma otterremo conflitti di inserimento per dati validi.

Può fare PK(metrico, ts, dati) - ma questo aumenterà notevolmente il suo volume, che non utilizzeremo.

Pertanto, l'opzione più corretta è creare un indice regolare non univoco (metrico, ts) e affrontare i problemi dopo il fatto, se si presentano.

"La guerra clonica è iniziata"

È successo qualche tipo di incidente e ora dobbiamo distruggere i record clone dal tavolo.

Modelliamo i dati originali:

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

Qui la nostra mano ha tremato tre volte, Ctrl+V si è bloccato e ora...

Innanzitutto comprendiamo che la nostra tabella può essere molto grande, quindi dopo aver trovato tutti i cloni, è consigliabile letteralmente “ficcare il dito” per eliminare record specifici senza effettuarne una nuova ricerca.

E c'è un modo: questo indirizzamento da parte di ctid, l'identificatore fisico di un record specifico.

Cioè, prima di tutto, dobbiamo raccogliere il ctid dei record nel contesto del contenuto completo della riga della tabella. L'opzione più semplice è trasformare l'intera riga in testo:

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

È possibile non lanciare?In linea di principio è possibile nella maggior parte dei casi. Fino a quando non inizi a utilizzare i campi in questa tabella tipi senza operatore di uguaglianza:

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ì, vediamo immediatamente che se c'è più di una voce nell'array, questi sono tutti cloni. Lasciamoli semplicemente:

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 chi ama scrivere più brevePuoi anche scriverlo così:

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

Poiché il valore della stringa serializzata in sé non ci interessa, lo abbiamo semplicemente eliminato dalle colonne restituite della sottoquery.

Resta solo un po' da fare: fai in modo che DELETE utilizzi il set che abbiamo ricevuto:

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

Controlliamo noi stessi:

[Guarda spiegare.tensor.ru]

Sì, è tutto corretto: i nostri 3 record sono stati selezionati per l'unica Seq Scan dell'intera tabella, e il nodo Elimina è stato utilizzato per cercare i dati passaggio singolo con Tid Scan:

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

Se hai cancellato molti record, non dimenticare di eseguire ANALISI DEL VUOTO.

Controlliamo una tabella più grande e con un numero maggiore di duplicati:

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;

[Guarda spiegare.tensor.ru]

Quindi, il metodo funziona con successo, ma deve essere utilizzato con una certa cautela. Perché per ogni record eliminato, c'è una pagina di dati letta in Tid Scan e una in Elimina.

Fonte: habr.com