Limpando registros clones de uma tabela sem PK

Há situações em que para uma tabela sem chave primária ou algum outro índice exclusivo, devido a um descuido, são incluídos clones completos de registros já existentes.

Por exemplo, os valores de uma métrica cronológica são gravados no PostgreSQL usando um fluxo COPY e, em seguida, ocorre uma falha repentina e parte dos dados completamente idênticos chega novamente.

Como livrar o banco de dados de clones desnecessários?

Quando PK não é um ajudante

A maneira mais fácil é evitar que tal situação ocorra em primeiro lugar. Por exemplo, role CHAVE PRIMÁRIA. Mas nem sempre isso é possível sem aumentar o volume de dados armazenados.

Por exemplo, se a precisão do sistema de origem for maior que a precisão do campo no banco de dados:

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}

Você percebeu? A contagem regressiva em vez de 00:00:02 foi registrada no banco de dados com ts um segundo antes, mas permaneceu bastante válida do ponto de vista do aplicativo (afinal, os valores dos dados são diferentes!).

Claro que você pode fazer isso PK(métrica, ts) - mas teremos conflitos de inserção de dados válidos.

Pode fazer PK (métrica, ts, dados) - mas isso aumentará muito o seu volume, que não utilizaremos.

Portanto, a opção mais correta é fazer um índice regular não exclusivo (métrica, ts) e lidar com os problemas após o fato, caso eles surjam.

"A guerra clônica começou"

Aconteceu algum tipo de acidente e agora temos que destruir os registros do clone da tabela.

Vamos modelar os dados originais:

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

Aqui nossa mão tremeu três vezes, Ctrl+V travou, e agora...

Primeiro vamos entender que nossa tabela pode ser muito grande, então depois de encontrarmos todos os clones, é aconselhável literalmente “cutucar o dedo” para deletar registros específicos sem pesquisá-los.

E existe tal maneira - esta endereçamento por ctid, o identificador físico de um registro específico.

Ou seja, antes de mais nada, precisamos coletar o ctid dos registros no contexto do conteúdo completo da linha da tabela. A opção mais simples é converter a linha inteira em 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)"}

É possível não lançar?Em princípio, é possível na maioria dos casos. Até você começar a usar campos nesta tabela tipos sem 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

Sim, vemos imediatamente que se houver mais de uma entrada no array, todos serão clones. Vamos apenas deixá-los:

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 quem gosta de escrever mais curtoVocê também pode escrever assim:

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

Como o valor da string serializada em si não é interessante para nós, simplesmente o descartamos das colunas retornadas da subconsulta.

Resta apenas um pouco a fazer - fazer DELETE usar o conjunto que recebemos:

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

Vamos nos verificar:

[veja explica.tensor.ru]

Sim, está tudo correto: nossos 3 registros foram selecionados para o único Seq Scan de toda a tabela, e o nó Delete foi usado para buscar dados passagem única com Tid Scan:

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

Se você apagou muitos registros, não se esqueça de executar VACUUM ANALYZE.

Vamos verificar se há uma tabela maior e com maior número de duplicatas:

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;

[veja explica.tensor.ru]

Portanto, o método funciona com sucesso, mas deve ser usado com cautela. Porque para cada registro excluído, há uma página de dados lida no Tid Scan e outra no Delete.

Fonte: habr.com