Borrar rexistros de clonación dunha táboa sen PK

Hai situacións nas que a unha táboa sen chave primaria ou algún outro índice único, debido a un descoido, inclúense clons completos de rexistros xa existentes.

Por exemplo, os valores dunha métrica cronolóxica escríbense en PostgreSQL mediante un fluxo COPY, e despois prodúcese un fallo repentino e parte dos datos completamente idénticos chegan de novo.

Como eliminar a base de datos de clons innecesarios?

Cando PK non é un axudante

O xeito máis sinxelo é evitar que tal situación se produza en primeiro lugar. Por exemplo, tirar CLAVE PRIMARIA. Pero isto non sempre é posible sen aumentar o volume de datos almacenados.

Por exemplo, se a precisión do sistema fonte é maior que a precisión do campo da 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}

Notaches? A conta atrás en lugar de 00:00:02 rexistrouse na base de datos con ts un segundo antes, pero permaneceu bastante válida desde o punto de vista da aplicación (a fin de contas, os valores dos datos son diferentes!).

Por suposto que podes facelo PK(métrico, ts) - pero despois obteremos conflitos de inserción para datos válidos.

Pode facer PK(métrica, ts, datos) - pero isto aumentará moito o seu volume, que non usaremos.

Polo tanto, a opción máis correcta é facer un índice regular non único (métrica, ts) e tratar os problemas despois do feito se xorden.

"A guerra clónica comezou"

Ocorreu algún tipo de accidente e agora temos que destruír os rexistros clonados da táboa.

Modelemos os datos orixinais:

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í a nosa man tremeu tres veces, Ctrl+V quedou atascado e agora...

En primeiro lugar, entendamos que a nosa táboa pode ser moi grande, polo que despois de atopar todos os clons, é aconsellable que literalmente "foquemos o dedo" para eliminar rexistros específicos sen buscalos de novo.

E hai tal forma - esta direccionamento por ctid, o identificador físico dun rexistro específico.

É dicir, en primeiro lugar, necesitamos recoller o ctid dos rexistros no contexto do contido completo da fila da táboa. A opción máis sinxela é lanzar toda a liña 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)"}

É posible non emitir?En principio, é posible na maioría dos casos. Ata que comeces a usar os campos desta táboa tipos sen operador de igualdade:

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

Si, inmediatamente vemos que se hai máis dunha entrada na matriz, todos son clons. Deixámolos:

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 os que lles gusta escribir máis brevementeTamén podes escribilo así:

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

Dado que o valor da cadea serializada en si non é interesante para nós, simplemente eliminámolo das columnas devoltas da subconsulta.

Só queda un pouco por facer: fai que DELETE use o conxunto 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[]);

Comprobámonos:

[Mira explicar.tensor.ru]

Si, todo é correcto: os nosos 3 rexistros foron seleccionados para a única exploración secuencial de toda a táboa e o nodo Eliminar utilizouse para buscar datos pase único con Tid Scan:

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

Se borraches moitos rexistros, non esquezas executar ANALIZACIÓN DE VACIO.

Comprobamos se hai unha táboa máis grande e cun maior 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]

Polo tanto, o método funciona correctamente, pero debe usarse con certa precaución. Porque por cada rexistro que se elimina, hai unha páxina de datos lida en Tid Scan e outra en Eliminar.

Fonte: www.habr.com