Borrar registros clonados de una tabla sin PK

Hay situaciones en las que a una tabla sin una clave principal o algún otro índice único, por un descuido se incluyen clones completos de registros ya existentes.

Por ejemplo, los valores de una métrica cronológica se escriben en PostgreSQL usando una secuencia COPY, y luego ocurre una falla repentina y parte de los datos completamente idénticos llegan nuevamente.

¿Cómo eliminar de la base de datos clones innecesarios?

Cuando PK no es un ayudante

La forma más sencilla es, en primer lugar, evitar que se produzca una situación de este tipo. Por ejemplo, tira LLAVE PRIMARIA. Pero esto no siempre es posible sin aumentar el volumen de datos almacenados.

Por ejemplo, si la precisión del sistema fuente es mayor que la precisión del campo en la base de datos:

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 diste cuenta? La cuenta regresiva en lugar de 00:00:02 se registró en la base de datos con ts un segundo antes, pero siguió siendo bastante válida desde el punto de vista de la aplicación (después de todo, ¡los valores de los datos son diferentes!).

Claro que puedes hacerlo PK(métrico, ts) - pero luego obtendremos conflictos de inserción para datos válidos.

Puede hacer PK(métrica, ts, datos) - pero esto aumentará mucho su volumen, que no utilizaremos.

Por tanto, la opción más correcta es hacer un índice regular no único. (métrico, ts) y lidiar con los problemas después del hecho si surgen.

"La guerra clónica ha comenzado"

Ocurrió algún tipo de accidente y ahora tenemos que destruir los registros clonados de la tabla.

Modelemos los datos 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)
;

Aquí nuestra mano tembló tres veces, Ctrl+V se atascó, y ahora...

Primero, entendamos que nuestra tabla puede ser muy grande, por lo que después de encontrar todos los clones, es recomendable que literalmente "pinchemos con el dedo" para eliminarlos. registros específicos sin volver a buscarlos.

Y existe tal manera - esta direccionamiento por ctid, el identificador físico de un registro específico.

Es decir, en primer lugar, debemos recopilar el ctid de los registros en el contexto del contenido completo de la fila de la tabla. La opción más sencilla es convertir toda la línea en texto:

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

¿Es posible no lanzar?En principio, es posible en la mayoría de los casos. Hasta que empieces a utilizar los campos de esta tabla tipos sin operador de igualdad:

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í, inmediatamente vemos que si hay más de una entrada en la matriz, todas son clones. Dejémoslos:

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)

Para aquellos a quienes les gusta escribir más breve.También puedes escribirlo así:

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

Dado que el valor de la cadena serializada en sí no nos interesa, simplemente lo eliminamos de las columnas devueltas de la subconsulta.

Sólo queda un poco por hacer: haz que DELETE use el conjunto que recibimos:

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

Comprobemos nosotros mismos:

[mira explicar.tensor.ru]

Sí, todo es correcto: nuestros 3 registros fueron seleccionados para el único Seq Scan de toda la tabla y el nodo Eliminar se usó para buscar datos. una sola pasada con Tid Scan:

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

Si borraste muchos registros, no olvide ejecutar ANÁLISIS DE VACÍO.

Comprobemos si hay una tabla más grande y con mayor número de duplicados:

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;

[mira explicar.tensor.ru]

Por tanto, el método funciona con éxito, pero debe utilizarse con cierta precaución. Porque por cada registro que se elimina, hay una página de datos leída en Tid Scan y otra en Eliminar.

Fuente: habr.com